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@xxxxxxxxx> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment:
signature.asc
Description: Digital signature
