Обсуждение: Bulk insert new and update out of temp table

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

On Mon, Mar 05, 2007 at 08:21:10AM -0800, Dustin Withers wrote:
> 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

<snip>

> 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.

Your problem is that the table named "Item" in your FROM clause is not
the table you're updating, so you end up with a cross-join. I think
this should work:

UPDATE Item SET
  Price=Item_Staging.Price
FROM Item_Staging
WHERE Item.Number = Item_Staging.Number

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.