Updating duplicate records in same table

[MARKAND911]

I have a table named "Table1"
Its schema with data is like this

Id   col2   col3   col4  col5  isUnique   dbUnique
1    red    3M     1995  UK      1          null
2    red    3M     1995  UK      0          null 
2    red    3M     1995  UK      0          null  
3    red    3M     1995  UK      0          null 
4    pink   A4     2002  USA     1          null
5    pink   A4     2002  USA     0          null

Now my question is that, that i want to update dbUnique column of this table, such that dbUnique should contain Id of Table1 where isUnique is 1 in all duplicate rows based on col2,col3,col4 & col5

So my updated data will look like this

Id   col2   col3   col4  col5  isUnique   dbUnique
1    red    3M     1995  UK      1          1
2    red    3M     1995  UK      0          1
2    red    3M     1995  UK      0          1
3    red    3M     1995  UK      0          1 
4    pink   A4     2002  USA     1          4
5    pink   A4     2002  USA     0          4

this table has over 50,000 rows.

How can i do this in MS SQL 2008

[debasisdas]

What you have tried so far ?

[MARKAND911]

THis i tried

UPDATE ra
SET ra.dbUnique= rb.col5
FROM table1 ra, table1 rb
WHERE ra.col2 = rb.col2
and ra.col2=rb.col2 
and ra.col3=rb.col3
and ra.col4=rb.col4

[BitBlt]

Here's a snippet of code to update the appropriate column. My table name is a different than yours...but I think you can figure out what to do.

update b 
set b.dbUnique = a.id
from dbo.mytablewithdups a
inner join dbo.mytablewithdups b
on a.col2 = b.col2
and a.col3 = b.col3
and a.col4 = b.col4
and a.col5 = b.col5
where a.isUnique = 1

The important part to notice is that you are joining the table to itself but differentiating selection criteria on the "sides" of the join. The "a" side is the unique rows only, the "b" side is not specified as either unique or non-unique rows. But by not specifying that the "b" side be non-unique, you include all the unique rows that might not have non-unique counterparts. Then by using the columns from the "b" side, and the joined id from the "a" (unique) side, you guarantee that every row will have the dbUnique column populated.

Kind of a long-winded explanation, but there you go. Hope it helps!

Edit: This was actually tested on SQL2008, using additional data to test the boundary conditions.