Re: Updating one table with data from another

On 2009-08-18, drew <iamdrewhayes(at)gmail(dot)com> wrote:
> Hey all,
> There are two things I need to do:
> 1. Update existing rows with new data
> 2. Append new rows
>
> I need to update only some of the fields table1 with data from
> table2. These tables have the exact same fields.
>
> So here's what I have currently for appending new rows (rows where CID
> does not currently exist in table1, but have been added to table2):
> INSERT INTO table1 (field1, field2, ...)
> SELECT field1, field2, ...
> FROM table2
> WHERE NOT EXISTS (SELECT CID FROM table1);
>
>
> But something is wrong with the logic there and I'm not quite getting
> it.

the where clause is wrong.

WHERE NOT EXISTS (SELECT 1 FROM table1 where table1.cid=table2.cid);

or

WHERE NOT cid IN (SELECT CID FROM table1);