3

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

웃웃웃웃웃
  • 11,829
  • 15
  • 59
  • 91
  • 1
    Maybe use of `NOT EXISTS` could work? `update t1 set column = 'value' WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE column='value')` This would update the column to same value unless you have another reference key tho. – Mad Dog Tannen May 19 '14 at 08:58
  • Maybe this post can help you : http://stackoverflow.com/a/6734266/1788704 – kmas May 19 '14 at 09:01

1 Answers1

3

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
)
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Which one is faster join or writing sub query inside where condition? – 웃웃웃웃웃 May 19 '14 at 09:32
  • I always prefer join on indexed columns however sometimes sub-queries also works good if the where condition is done on indexed columns. But as I said I prefer JOIN over sub-query. check here more on this http://stackoverflow.com/questions/3856164/sql-joins-vs-sql-subqueries-performance – Abhik Chakraborty May 19 '14 at 09:37