update table based on another 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 2005 Forums

Transact-SQL (2005)

update table based on another table

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-07 : 07:32:56

Hi,

I have a te table Cartons that contains carton_id,cartonstatus_id,packlist_id

I have another table called Packlist with packlist_id, packlist_status

The third table cartonStatus contains cartonstatus_id, cartonrank

Different cartons can have the same packlist and have different statuses.

I need to update the packlist status with the highest status according to the carton status.

For example carton table
carton_id cartonstatus_id packlist_id
12 2 12345
13 2 12345
21 4 12345
23 1 12345

CartonStatus table
cartonstatus_id cartonrank
1 3
2 2
3 1
4 5

In this case cartonstatus_id 2 has the highest rank (rank 2 after rank 1).
I need to update packlist with packlist_statusid =2.
I need to run through all the packlist_ids in the packlist table and update their status.

How can i do that?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 08:34:15

[code]UPDATE p
SET p.packlist_status = rnk.rank
FROM Packlist p
CROSS APPLY (SELECT TOP 1 rank
FROM CartonStatus cs
INNER JOIN carton c
ON c.cartonstatus_id = cs.cartonstatus_id
WHERE c.packlist_id = p.packlist_id
ORDER BY rank DESC)rnk
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-07 : 09:53:44

Thanks for the help :)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 10:26:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources