Pre Exam Test Practice (SSC | Banking | IAS | GATE | State Level | CLAT | Railways | Insurance | Teaching Exams | NDA | Defence | IES |State Exam )

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

Related Hot Questions

Government Jobs Opening