MYSQL update if not exists else do nothing

My question is how do i update a value in a table if it does not exists on another table.I checked INSERT ... ON DUPLICATE KEY UPDATE but it describes about inserting something which updates and not insert.

My situation is like, i have two tables say (t1,t2). I want to update a column in t1 with a value if its not present in t2. Otherwise increment the value and try the update again. So i want something like

update t1 set column = 'value' if it does not exists in t2 

Can somebody suggest a solution

1 Answer

Here is a way to do it using the JOIN.

create table tab1 (id int , val int); insert into tab1 values (1,1),(2,3),(3,5); create table tab2 (id int , val int); insert into tab2 values (4,1),(2,3),(3,5); 

In the above tab1 (id = 1) not available in tab2 and using the following command we can update such values

update tab1 t1 left join tab2 t2 on t1.id = t2.id set t1.val = case when t2.id IS NULL then 8 else t1.val end 

The output after the update command will look like

mysql> select * from tab1 ; +------+------+ | id | val | +------+------+ | 1 | 8 | | 2 | 3 | | 3 | 5 | +------+------+ 

Also you can use EXIST which is also pretty better than doing left join

update tab1 t1 set t1.val = 10 where NOT EXISTS ( select 1 from tab2 where tab2.id = t1.id )