Hello All, I have an accounting system doing nightly dumps of tables out of a 4D database. It only dumps out stuff that has changed within the last 24 hours. So these dumps only contain the last changed data. The dumps get inserted into temp tables. Now the problem comes from getting the info out of the temp tables and into the production tables. I was trying to get a join update and insert working: UPDATE Item SET Price=Item_Staging.Price FROM Item INNER JOIN Item_Staging ON Item.Number = Item_Staging.Number INSERT INTO Item(Number, Price, Description) SELECT S.Number, S.Price, S.Description FROM Item_Staging S LEFT JOIN Item I ON S.Number = I.Number WHERE I.Number IS NULL These are both coming from http://blogs.meetandplay.com/WPierce/archive/2006/12/22/Delete_Absent_RowsUpdate_Existing_RowsInsert_New_Rows.aspx and I suspect if I was using SQL Server they would work. No matter how I modified these I could never get them to work correctly. Ultimately I changed to subquery versions of them: UPDATE "Item" SET Price=Item_TEMP.Price FROM "Item_TEMP" WHERE EXISTS (SELECT * FROM "Item_TEMP" WHERE "Item_TEMP"."Number" = "Item"."Number") INSERT INTO "Item" ("Number", "Price", "Description") (SELECT "Number", "Price", "Description" FROM "Item_TEMP" LEFT JOIN "Item" ON "Item_TEMP"."Number" = "Item"."Number" WHERE "Item"."Number" IS NULL) It seems the insert works but the update does not and causes all rows updated to look like the last row in the temp table. I would like to make these join insert and updates but ultimately I would just like both to work. Any ideas? Thanks, -dustin
