I have two tables, they have a same column id
, but table1
has more id
s than table2
. Now I want to find those id
a in table1
but not exist in table2
, and insert
them into table2
, and set their value of count as 0.
I tried the following code but it says syntax error, unexpected IF
.
if not exists(select * from table1 where table1.id = table2.id)
begin
insert into table2 (id, count) values (table1.id, 0)
end
You can do this with a single insert . . . select
statement:
insert into table2(id, count)
select id, 0
from table1 t1
where not exists (select 1 from table2 t2 where t2.id = t1.id);
I am guessing that you are using MySQL if you are getting an error on if
(if
is only allowed in procedure/function/trigger code). But even if if
where allowed, the query in exists
references table2.id
and there is no table2
in the from
clause. So that would be the next error.
LEFT JOIN also could be used here
insert into table2(id, count)
select t1.id, 0
from table1 t1 left join table2 t2
on t1.id = t2.id
where t2.id is null;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With