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
     from table
     group by y) as t1
    using (y)
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!
    
asked Oct 6, 2015 by nikhilapatil
0 votes
14 views



Related Hot Questions



Government Jobs Opening


...