Update unique rows in SQL

I have a table

id | col1 | col3| col4
1  | x    |  r  |
2  | y    |  m  |
3  | z    |  p  |
4  | x    |  r  |

i have to update all unique rows of this table i.e

  id | col1 | col3| col4
  1  | x    |  r  |  1
  2  | y    |  m  |  1
  3  | z    |  p  |  1
  4  | x    |  r  |  0

i can fetch unique rows by

  开发者_运维知识库select distinct col1,col2 from table

.But how can i identify these rows in order to update them.Please help.

---

You can use the group by to pick unique result:

SELECT MIN(ID) AS ID FROM TABLE GROUP BY COL1, COL3;

  id | col1 | col3
  1  | x    |  r  
  2  | y    |  m  
  3  | z    |  p  

Then

UPDATE TABLE SET col4 = 1 WHERE ID IN (SELECT MIN(ID) FROM TABLE GROUP BY COL1, COL3);

Restriction is that the id column should be unique.

---

If it is a small enough table, here is what you can do

Step 1: Update everything to 1

Update Table Set Col4 = 1

Step 2: Update all dups to 0 (OTTOMH)

Update Table
Set Col4 = 0
From 
(
    Select Col1, Min (Id) FirstId
    From Table
    Group By Col1
    Having Count (*) > 1
) Duplicates
Where Table.Col1 = Duplicates.Col1
And Table.Id <> Duplicates.FirstId

---

You can also try:

UPDATE test
   SET col4 = 1
 WHERE id IN
     (
       SELECT t1.id
         FROM table_name t1
         LEFT JOIN table_name t2
           ON t2.id < t1.id
          AND t2.col1 = t1.col1
          AND t2.col3 = t1.col3
        WHERE t2.id IS NULL
     )

---

One more slightly convoluted option, to set both 0 and 1 values in one hit:

update my_table mt
set col4 = (
    select case when rn = 1 then 1 else 0 end
    from (
        select id,
            row_number() over (partition by col1, col3 order by id) as rn
        from my_table) tt
    where tt.id = mt.id);

4 rows updated.

select * from my_table order by id;

        ID COL1 COL3       COL4
---------- ---- ---- ----------
         1 x    r             1
         2 y    m             1
         3 z    p             1
         4 x    r             0

This is just using row_number() to decide which of the unique combinations is first, arbitrarily using the lowest id, assigning that the value of one, and everything else zero.