Update only the Distincts - 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

Transact-SQL (2000)

Update only the Distincts

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2009-01-30 : 09:51:24

Hello there i have this data:



Number    X    Y
12456     0    123
12456     0    123
14789     0    145

and i want to update X but only the distinct ones. if i update the x Field to 0 then the result is this:


Number    X    Y
12456     1   123
14789     1   145

Because there are two records that have the same Number and Y. How to use something like distinct so i can update only the distinct ones? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 10:01:46

[code]
UPDATE t
SET t.X=1
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Number,Y ORDER BY Y) AS Seq,X
FROM Table)t
WHERE t.Seq=1
[/code]

CVDpr
Starting Member

41 Posts

Posted - 2009-01-30 : 10:23:34

'ROW_NUMBER' is not a recognized function name.?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-30 : 11:20:56

May be you need to delete duplicate first.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 11:48:12

quote:
Originally posted by CVDpr

'ROW_NUMBER' is not a recognized function name.?

are you using sql 2005 with compatibility level 90? run below and post back.


EXEC sp_dbcmptlevel 'your database',90

CVDpr
Starting Member

41 Posts

Posted - 2009-01-30 : 14:25:37

MMm, the database is compatibility 80 and i cant change that because the database is not mine. Its there another way so i can do this in compatibility 80/sql server 2000? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 10:13:10

something like


SELECT IDENTITY(int,1,1) AS ID, * INTO #Temp
FROM Table

UPDATE r
SET r.X = 1
FROM(
SELECT (SELECT  COUNT(*) FROM Table WHERE Number=t.Number AND ID <=t.ID) AS Seq, *
FROM Table t
)r
WHERE r.Seq=1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-31 : 15:27:02

quote:

Originally posted by visakh16

something like


SELECT IDENTITY(int,1,1) AS ID, * INTO #Temp
FROM Table

UPDATE r
SET r.X = 1
FROM(
SELECT (SELECT  COUNT(*) FROM #Temp WHERE Number=t.Number AND ID <=t.ID) AS Seq, *
FROM #Temp t
)r
WHERE r.Seq=1

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-02-01 : 06:40:19

update t
set x=1 where
t.number in (select number from t t2 having count(*)=1)

not sure if this will work - join to a subquery if not.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-01 : 07:03:21

quote:
Originally posted by LoztInSpace

update t
set x=1 where
t.number in (select number from t t2 group by number having count(*)=1)

not sure if this will work - join to a subquery if not.

i think u have to use group by in ur subquery

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-02-01 : 07:05:13

Yeah thanks. I knew there was something fundemental missing. I'm not in front of SQL right now.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-01 : 10:23:54

quote:
Originally posted by bklr

quote:
Originally posted by LoztInSpace

update t
set x=1 where
t.number in (select number from t t2 group by number having count(*)=1)

not sure if this will work - join to a subquery if not.

i think u have to use group by in ur subquery

This won't give you expected results. Try Visakh's modified query.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-02-02 : 00:31:08

Ah yes, I now see it doesn't update either one of the duplicates. The old 'which one do I update?' question.
While I agree that Visakh's method works, it does update one of the duplicate rows at random (not good in my book).
Maybe a better next step would be to ask "For a set of duplicates, which of the rows needs to be updated and how is it identified?".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 00:35:14

As per OPs sample data he had no extra field which distingusihes each duplicated row, so we need to know about waht extra fields he has before we can determine which row is to be updated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-02 : 01:05:56

Moving to the 2000 forum...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources