MySQL Update column with statement

Question

Hi everyone,

I would like to do something like the following

UPDATE table SET column = other_column IN(complex_statement)

My question is whether this wold cause the complex statement to be evaluated for each row or not, which would obviously not very good for performance.

The alternative would be doing

UPDATE table SET column = 0;
UPDATE table SET column = 1 WHERE other_column IN(complex_statement)

Answer 1

This depends on whether the "other Complex statement" is a "Correlated" subquery or not.. A "Correlated" subquery is one where the results depend on the value of the row in the "outer" query.

So, if the complex statement has a reference to a column on your outer query table, then it is a "Correlated" subquery and the answer would be yes, it will be evaluated foreach row. Otherwise, No.