Re: Bulk insert new and update out of temp table

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.