Compare and Update records in 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 2000 Forums

SQL Server Development (2000)

Compare and Update records in same table

Author

Topic

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 07:23:56

Hi Group,
I have 2 sets of 'matched' records in a table with a start date field of type datetime. I want to update a field called flag in the table with 1 for the earlier date and 2 for the later date.

example

Start Date Flag
12/07/2006 1
17/07/2006 2

regards

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-30 : 07:48:44

update tbl
set flag = case when dte = (select max(t2.dte) from tbl t2 where t1.fld = t2.fld) then 2 else 1 end
from tbl t1

where fld is the match field for the pairs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 11:18:39

nr does this update BOTH records? I do not have access to the system at the moment but looking at the syntax it looks like it's just one record thats being updated.

quote:
Originally posted by nr

update tbl
set flag = case when dte = (select max(t2.dte) from tbl t2 where t1.fld = t2.fld) then 2 else 1 end
from tbl t1

where fld is the match field for the pairs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-01 : 23:25:07

It'll update all rows on the table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources