Please help with update 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

SQL Server 2005 Forums

Transact-SQL (2005)

Please help with update statement

Author

Topic

resuof
Starting Member

3 Posts

Posted - 2008-09-21 : 21:49:42

I am having a bit of trouble. I would like to update a group of records to the max(PKID) of each group. Below is the sql that I used to return my result set

select distinct t1.PKID, t1.Date, t1.FKID
from tblTable t1,tblTable t2
where t1.FKID = t2.FKID
and t1.Date = t2.Date

1 2003-06-04 00:00:00.000 110698
5 2003-06-04 00:00:00.000 110698
6 2003-06-04 00:00:00.000 110698
7 2003-06-04 00:00:00.000 110698
9 2003-06-03 00:00:00.000 111022
10 2003-06-03 00:00:00.000 111022
12 2003-06-03 00:00:00.000 111024
13 2003-06-03 00:00:00.000 111024

1 thru 7 is a group for 110698 on 6/4/2003. I would update a field to show the max ID in that group is 7.

9 thru 10 is a group for 111022on 6/3/2003. I would update a field to show the max ID in that group is 10

etc...

Can someone help? Thank you in advance!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-21 : 22:17:58

why UPDATE ? you can do that with simple SELECT query with GROUP BY


select t1.Date, t1.FKID, max(t1.PKID)
from   tblTable t1
       inner join tblTable t2
       on  t1.FKID = t2.FKID
       and t1.Date = t2.Date
group by t1.Date, t1.FKID

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

resuof
Starting Member

3 Posts

Posted - 2008-09-21 : 22:57:24

I need to update the information to meet a new table structure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 04:13:47

quote:
Originally posted by resuof

I need to update the information to meet a new table structure.

then use select query given by Khtan for your update

UPDATE t
SET t.MaxPKCol=tmp.maxPK
FROM tblTable t
INNER JOIN (select t1.Date, t1.FKID, max(t1.PKID) AS maxPK
from   tblTable t1
       inner join tblTable t2
       on  t1.FKID = t2.FKID
       and t1.Date = t2.Date
group by t1.Date, t1.FKID)tmp
ON tmp.Date=t.Date
AND tmp.FKID=t.FKID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-22 : 04:52:13

[code]UPDATE f
SET f.NewCol = CASE WHEN f.RecID = 1 THEN 1 ELSE 0 END
FROM (
SELECT NewCol,
ROW_NUMBER() OVER (PARTITION BY FkID ORDER BY PkID DESC) AS RecID
) AS f[/code]

E 12°55'05.63"
N 56°04'39.26"

resuof
Starting Member

3 Posts

Posted - 2008-09-22 : 16:42:42

Thank you visakh16. I don't know why I had such a hard time grasping the solution but I couldn't. Many thanks. Peso? Your solution looks very interesting but I don't know if I fully get what it is doing. Would you mind appeasing me by explaining?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources