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
