Update distinct numbers - 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 2005 Forums

Transact-SQL (2005)

Update distinct numbers

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2008-12-19 : 13:52:59

Hello,
I have a table of duplicate numbers, how do i update the distinct numbers and set the value of a column [isunique] = 1

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-19 : 15:50:36

I don't know what your tables look like, but this will give you a start
ECLARE @Table TABLE (id int identity (1,1), Num int,isunique tinyint)
INSERT INTO @Table
SELECT 1,0 UNION ALL
SELECT 1,0 UNION ALL
SELECT 2,0 UNION ALL
SELECT 3,0 UNION ALL
SELECT 4,0 UNION ALL
SELECT 4,0

UPDATE @TABLE
SET isunique = 1
FROM
@Table a
INNER JOIN
(select num,[res] = count(*) from @table group by num having count(*) = 1) b
ON
a.num = b.num

select * from @table

Jim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-19 : 16:00:46

quote:
Originally posted by missMac

Hello,
I have a table of duplicate numbers, how do i update the distinct numbers and set the value of a column [isunique] = 1

Thanks

Update tablename
Set isunique = (case when col1 in(select col1 from tablename group by id
Having count(Col1)=1) then 1 else 0 end)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-19 : 22:35:46

SELECT id,num,CASE WHEN num in(SELECT num FROM @table GROUP BY num
Having count(*)=1) THEN 1 ELSE 0 END AS 'isunique' FROM @table

just using case function try this once

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-19 : 22:52:53

quote:
Originally posted by bklr

SELECT id,num,CASE WHEN num in(SELECT num FROM @table GROUP BY num
Having count(*)=1) THEN 1 ELSE 0 END AS 'isunique' FROM @table

just using case function try this once

What did I use? Why are you repeating same thing ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-19 : 22:55:00

quote:
Originally posted by sodeep

quote:
Originally posted by bklr

SELECT id,num,CASE WHEN num in(SELECT num FROM @table GROUP BY num
Having count(*)=1) THEN 1 ELSE 0 END AS 'isunique' FROM @table

just using case function try this once

What did I use? Why are you repeating same thing ?

UR o\p is like this so check it once
so that i had modified and send the query

1 1 0
2 1 0
3 2 0
4 3 0
5 4 0
6 4 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources