Lahiru Chandima Lahiru Chandima - 1 year ago 71
MySQL Question

SQL: update a record if a certain kind of a record is not in the same table

I want to update a certain record of a table only if certain another record does not already exist in the table.

I tried a SQL similar to following.

update mytable
set val = 'someval'
where id = 'someid' and
0 = (select count(*) from mytable where col='val2');

This fails with following error.

You can't specify target table 'mytable' for update in FROM clause

Only one process is updating this table, so preserving the atomicity of the operation is not necessary.

I know I can do this using two SQL queries, but is there a way to do this in a single query?

Answer Source

Because you are referring to the same table, the best way to do this uses LEFT JOIN:

update mytable t left join
       mytable t2
       on t2.col = 'val2'
    set val = 'someval'
    where = 'someid' and t2.col is null;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download