Comparing /Updating records in the same 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)

Comparing /Updating records in the same table

Author

Topic

jjcyeshua
Starting Member

1 Post

Posted - 2008-02-22 : 17:46:26

Hi... I need some help and suggestions..

Im trying to create a query that would update a record/s this how the table looks like

Grp_Id, App_Id, Group_Status, Update_DT,
1 54 296 11/01/07
1 54 NULL 11/15/07
1 54 NULL 12/01/07
2 54 294 11/12/07
2 54 NULL 11/28/07

Basically i want to update all null with the old values.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-22 : 19:15:30

[code]

Update [TableName]
Set [App_ID] = a.[App_ID]
FROM (Select [Grp_ID],[App_ID],[Update_DT]
FROM [Tablename]
WHERE [App_ID] is null) a INNER JOIN [TableName] b on a.[Grp_ID] = b.[Grp_ID]
WHERE a.[Update_DT] < b.[Update_DT]
[/code]

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 03:41:41

quote:
Originally posted by dataguru1971

Update [TableName]
Set [App_ID] = a.[App_ID]
FROM (Select [Grp_ID],[App_ID],[Update_DT]
FROM [Tablename]
WHERE [App_ID] is null) a INNER JOIN [TableName] b on a.[Grp_ID] = b.[Grp_ID]
WHERE a.[Update_DT] < b.[Update_DT]

Poor planning on your part does not constitute an emergency on my part.

Looks like you have put it in opposite way.I think this is what you meant:-

Update a
Set a.[App_ID] = b.[App_ID]
FROM (Select [Grp_ID],[App_ID],[Update_DT]
      FROM [Tablename]
      WHERE [App_ID] IS NULL) a  
INNER JOIN [TableName] b 
on a.[Grp_ID] = b.[Grp_ID]
AND a.[Update_DT] > b.[Update_DT]
AND b.[App_Id] IS NOT NULL

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-23 : 14:08:17

Yep...got it backwards in my statement. Thanks for the catch.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources