Ask a Question
Advertise on boostr.in
Organizational Research By
Surprising Reserch Topic
will sql update affect its subquery during the update run
will sql update affect its subquery during the update run ¬†using -'mysql,sql,database,oracle,postgresql'
I'm just composing a complex update query which looks more or less like this:
update table join
¬†¬†¬†¬†(select y, min(x) as MinX
¬†¬†¬†¬†¬†group by y) as t1
set x = x - MinX
Which means that the variable x is updated based on the subquery, which also processes variable x - but couldn't this x already be modified by the running update command? Isn't this a problem? I mean, in normal programming you normally have to handle this explicitly, i.e. store new value to some other place from the old value and after the job is done, replace the old value with new... but how will SQL database do this?
I'm not interested in a single observation or experiment. I would like to have a snippet from the docs or sql standard that will say what is the defined behaviour in this case. I'm using MySQL, but answers valid also for other PostgresQL, Oracle, etc. and especially for SQL standard in general are appreciated. Thanks!
Oct 6, 2015
to add a comment.
Related Hot Questions
Government Jobs Opening