update only first row where duplicate fields exist

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 2008 Forums

Transact-SQL (2008)

update only first row where duplicate fields exist

Author

Topic

spinningtop
Starting Member

29 Posts

Posted - 2012-02-24 : 07:46:02

Hi

I have a table similar to the one below. I would like to update the info column (currently null) with a 1. However where the custid and date fields are duplicates I would only like to update the first row only according to the sorted uniqueid primary key field. So in the table below where the custid is 0003, only the row with uniqueid 000c would be updated the other 2 rows left as null.

Thanks for any help

custid date info uniqueid
0001 19970916 000a
0002 19940815 000b
0003 20020212 000c
0003 20020212 000d
0003 20020212 000e
0004 20040915 000f
0005 20001009 000g
0006 20050523 000h

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-24 : 07:49:01

You can use a CTE to do this as shown below. If you remove the last line (UPDATE statement) and uncomment the SELECT above that, you can see what is going to be updated without actually doing the update.

;WITH cte AS
(
	SELECT *,ROW_NUMBER() OVER (PARTITION BY custid ORDER BY uniqueid) AS RN
)
-- select * from cte where RN=1;
UPDATE cte SET info = 1 WHERE RN=1;

spinningtop
Starting Member

29 Posts

Posted - 2012-02-24 : 08:06:31

Thanks I'll check this and let you know how I got on

spinningtop
Starting Member

29 Posts

Posted - 2012-02-28 : 12:49:40

That's great. Works very well with 'FROM table' added
thanks.

malachi151
Posting Yak Master

152 Posts

Posted - 2012-02-28 : 13:07:19

An alternative way to write this, that, IMO is a little clearer, is:

UPDATE t
SET
	info = 1
FROM
	(SELECT *, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY uniqueid) AS ranking) t
WHERE ranking = 1

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources