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.
set val = 'someval'
where id = 'someid' and
0 = (select count(*) from mytable where col='val2');
You can't specify target table 'mytable' for update in FROM clause
Because you are referring to the same table, the best way to do this uses
update mytable t left join mytable t2 on t2.col = 'val2' set val = 'someval' where t.id = 'someid' and t2.col is null;