Insert into table when one value not exist in another table?

Question

I have two tables, they have a same column id, but table1 has more ids than table2. Now I want to find those ida 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

Solution 1

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.

Solution 2

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;

Comments

[consigliere]

I think this is the way to go!