Updating Multiple column of a row

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)

Updating Multiple column of a row

Author

Topic

anumodhc
Starting Member

13 Posts

Posted - 2009-08-11 : 01:24:01

Hi
I have a table for different reminder items like
#tempReminder
(
Id int,
Rem1 datetime,
Rem2 datetime,
Rem3 datetime
)

Each reminders having different type value like rem1 - 7, rem2 - 8 etc

Also have another table like

Expirings
(
Id int,
type int,
_date datetime
)

For an ID 100 there are 3 records in table Expirings

Id type _date
--------------------------------
100 7 8/11/2009
100 8 8/25/2009
100 9 9/5/2009

i want to update single row of #tempReminder from multiple rows of Expirings.

Please help

Thanks

AnumodH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-11 : 01:38:38

[code]
UPDATE R
SET Rem1 = E.Date1,
Rem2 = E.Date2,
Rem3 = E.Date3
FROM #tempReminder R
INNER JOIN
(
SELECT ID,
Date1 = max(CASE WHEN type = 7 THEN _date END),
Date2 = max(CASE WHEN type = 8 THEN _date END),
Date3 = max(CASE WHEN type = 9 THEN _date END)
FROM Expirings
GROUP BY ID
) E ON R.Id = E.Id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

anumodhc
Starting Member

13 Posts

Posted - 2009-08-11 : 02:01:37

Thanks a lot Khtan
Its working for me.
thanks again

AnumodH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources