Обсуждение: Updating one table with data from another
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. For the update part, here's what I have: UPDATE table1 SET field1 = table2.field1 field2 = table2.field2, ..., FROM table1 INNER JOIN table2 ON table1.CID=table2.CID; I'm not sure what's wrong with this one either. Any help would be greatly appreciated!
On 2009-08-18, drew <iamdrewhayes@gmail.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);