Help with duplicates - SQL Server Forums

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)

Help with duplicates

Author

Topic

cwizards
Starting Member

1 Post

Posted - 2013-02-04 : 09:53:58

Hi, I have a table with four fields (that are of interest), id, refnumber, dateadded and canceled. Previously the code selected the latest date (dateadded) and the refnumber to get the record required as there were duplicate refnumber records. As my new code utilises a table join method, I need to mark the old records as canceled (=1) leaving the most recent uncanceled (=0).
example
1 48394A0021 2002-01-01 0
2 54739A0040 2002-01-01 0
3 54739A0150 2002-01-01 0
4 54739A0150 2004-01-01 1
ids 3&4 are duplicate ref numbers and I need to set the canceled as shown (0 for the older one(s) and 1 for the latest)
Can this be done in a SQL statement?
Your help will be gratefully received
Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 12:27:49

[code]
UPDATE t
SET cancelled = CASE WHEN Seq=1 THEN 1 ELSE 0 END
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY refnumber ORDER BY dateadded DESC) AS seq,cancelled
FROM table
)t
[/code]

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-05 : 02:41:34

ROW_NUMBER() is not available in MS SQL 2000.
So this is the alternative solution
I assumed that if there is single refnumber then that has to be marked as 1 ( i.e. RecentDate)


DECLARE @example TABLE(refnumber VARCHAR(20), dateadded date, cancelled BIT)
INSERT INTO @example
SELECT '48394A0021', '2002-01-01', 0 union all  -- Single refnumber
SELECT '54739A0040', '2002-01-01', 0 union all  -- Single refnumber
SELECT '54739A0150', '2002-01-01', 0 union all
SELECT '54739A0150', '2004-01-01', 1            -- marked as 1 b'coz latest record for refnumber '54739A0150'
UPDATE @example SET
cancelled = 1 
FROM @example e 
JOIN (SELECT refnumber, MAX(dateadded) RecentDate
		FROM @example
		GROUP BY refnumber
	 ) t
ON e.refnumber= t.refnumber AND e.dateadded= t.RecentDate

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 02:46:59

didnt notice this is 2000 forum
Anyways, it should be this unless you've default constraint on cancelled field to make it 0


UPDATE @example SET
cancelled = CASE WHEN t.refnumber IS NOT NULL THEN  1 ELSE 0 END
FROM @example e 
LEFT JOIN (SELECT refnumber, MAX(dateadded) RecentDate
		FROM @example
		GROUP BY refnumber
	 ) t
ON e.refnumber= t.refnumber AND e.dateadded= t.RecentDate

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources