Update multiple rows in a joined table

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update multiple rows in a joined table

Author

Topic

djchrome
Starting Member

19 Posts

Posted - 2002-02-15 : 17:26:01

I have 3 tables: RequestReport, RequestItems, RequestItemResponse

There are multiple RequestItems for each RequestReport. And there are also multiple RequestItemResponse for each RequestItems (still with me?). Both the RequestReport and RequestItemResponse have a column for a status.

I would like the following to happen: When I change the status of a unique RequestReport I need all the related RequestItemResponses to change to the same status as the Report.

I still need to have the flexability to mark individual reqests as complete (which is why the status column is not ONLY in the report table)

I don't need to change any data in the RequestItems table, it is only for the purpose of joining the other 2 tables.

Thanks in advance for your words of wisdom.

Edited by - djchrome on 02/15/2002 17:26:39

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-15 : 17:50:24

This sounds like a good use for a trigger on the RequestReport table that issues the UPDATE statement to the RequestItemResponse. You'll need to work on the WHERE clause a little bit, but it will be something like

UPDATE RequestItemResponse
SET RequestItemResponse.Status = INSERTED.Status
FROM RequestItemResponse, INSERTED
WHERE RequestItemResponse.ReportID = INSERTED.ReportID

INSERTED and DELETED are special tables that exist only in the realm of triggers, and contain the data that was deleted or inserted. An UPDATE to a table fills both these magical tables as if it did a delete and then an insert of the data.

More info on Triggers can be found in BOL.

------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 02/15/2002 17:51:57

Edited by - AjarnMark on 02/15/2002 17:53:53

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-15 : 20:23:14

If you're using SQL Server 2000, you can also create foreign key constraints the perform cascade update actions, exactly like the feature in MS Access.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources