Multiple record updates in one statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple record updates in one statement

Author

Topic

Richard Steele
Starting Member

11 Posts

Posted - 2006-04-15 : 12:36:36

Is there a way to update all of the records in a table all at once using the results of a select of a different table's data?

For example.
There are two tables, each have a primary index of catnum. One table (invoice_items) contains the line items (catnum) of customer invoices. The other table (sales_history) is a sales history table. I want to select all data from the invoice_items and sum the sales for various time periods (sales_0to30days, sales_31to60days, etc.) I then want to update the sales_history table which has columns: catnum, sales_0to30, sales_31to60, etc.). I want to run this daily to update all records.

Any help would be appreciated. Many thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-15 : 12:47:19

U may use some similar statement as follows

UPDATE sales_history
SET sales_0to30 = i.[field Name], ....
FROM sales_history s
INNER JOIN invoice_items i on .... = ....
Where .... = ....

Read the UPDATE command Syntax in SQL Server Help (BOL)

Srinika

Richard Steele
Starting Member

11 Posts

Posted - 2006-04-15 : 14:50:57

Ok, thanks! That makes total sense. Now, how do I compute (sum) the sales totals for EACH of the various catnum sales for MULTIPLE time periods on the fly as part of this update?

I assume it would be part of the SET, something like....
However, how does the statement know which catnum to put in the "xwhatdoIputhere"

UPDATE sales_history
SET sales_0to30 = (SELECT catnum, SUM(Items.qtysold)
FROM items WHERE catnum="xwhatdoIputhere?"
AND sales_0to30days>=date() and sales_0to30days<=date()+30)
FROM sales_history s
INNER JOIN invoice_items i on .... = ....
Where .... = ....

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-15 : 18:55:31

Hint:

U can Inner Join several Derived Tables if u have different Criteria to be Updated for Each field

Or U can Use Case - When - Then if u want conditional updates

If u want to use ur way:
(I'm not sure ur condition of where clause and now u have an items table as well)


UPDATE sales_history
SET sales_0to30 = 
  (SELECT catnum, SUM(Items.qtysold) 
   FROM items WHERE 
   catnum = s.[Field Or Aggregate of Table Alias s]
   AND sales_0to30days>=date() getdate() and   
   sales_0to30days<=getdate()+30)
FROM sales_history s
INNER JOIN invoice_items i on .... = ....
Where .... = ....

If u give the table structure / sample data and expected results, v may be able to help u more

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources