SQL Server - updating distinct values

Question

I have 3 columns of data:

Column1 has duplicate values eg a a b b c c
Column2 has all NULL values
Column3 has other data that is not really important

I want to update Column2 with a value eh Hello but only for 1 instance of each value for column1. Eg, a = Hello but the 2nd instance of Hello is NULL, same with b c and so on.

I can find the distinct value by using this:

select distinct Column1 
from TABLENAME

But when I try to update a different column it breaks. What is wrong (probably a lot!!) with this:

update TABLENAME 
set Column2 = 'Hello'
where (select distinct Column1 from TABLENAME)

score 2 · Answer 1 · answered Dec 31 '19 at 15:58

You can try to use ROW_NUMBER window function make row number then only update with row number is 1.

update t1 
set Column2 = 'Hello'
FROM (
    select *,ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY Column3) rn
    from TABLENAME
) t1
where rn = 1

Results:

| Column1 | Column2 | Column3 |
|---------|---------|---------|
|       a |   Hello |       1 |
|       a |  (null) |       2 |
|       b |   Hello |       3 |
|       b |  (null) |       4 |
|       c |   Hello |       5 |
|       c |  (null) |       6 |

score 0 · Answer 2 · answered Dec 31 '19 at 15:58

0

Assuming it is your unique index, use Column 3.

UPDATE tablename SET column2 = 'Hello' WHERE column3 IN
(SELECT column3 from TableName GROUP BY column1)

answered Dec 31 '19 at 15:58

Tanner Clark

631
1
8
19

Will it? The group by will choose a column1 value within each subset of column1. Thus, it will only update the a single row from each grouping of column 1. – Tanner Clark Dec 31 '19 at 16:10

score 0 · Answer 3 · answered Dec 31 '19 at 16:06

0

You could also update only the rows that have odd numbers in Column 3.

UPDATE tablename SET column2 = 'Hello' WHERE column3 % 2 != 0

answered Dec 31 '19 at 16:06

ln29st

83
2
11

I almost wrote this! However, I think that could cause issues if there would be more than two values per distinct value of column1 – Tanner Clark Dec 31 '19 at 16:11
1

Are you able to create temp tables? select column1, max(column3) column3 into #temp1 from yourtable group by column1 update yourtable set column3 = 'Hello' where column3 in (select column3 from #temp1) – ln29st Dec 31 '19 at 16:22

score 0 · Answer 4 · answered Dec 31 '19 at 16:51

You can also use CROSS APPLY and CTE (Common Table Expression) to achieve this:

;with CTE AS
(SELECT t.Column1, t.Column2 
FROM   (SELECT DISTINCT Column1 
        FROM   TABLENAME) x 
       CROSS APPLY(SELECT TOP 1 *
                    FROM   TABLENAME 
                    WHERE  column1 = x.column1) t)
UPDTATE CTE
SET Column2 = 'Hello'

SELECT * FROM TABLENAME

score 0 · Answer 5 · answered Dec 31 '19 at 16:58

You could use a window function as

UPDATE TT
SET Col = B
FROM
(
  SELECT Col, ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col) RN
  FROM T
)TT INNER JOIN
(
  VALUES (1, 'Hello'), (2, NULL)
) TVC (A, B)
ON TT.RN = TVC.A;

Results:

+-------+
|  Col  |
+-------+
| Hello |
| NULL  |
| Hello |
| NULL  |
| Hello |
| NULL  |
+-------+

Or using a CASE expression as:

UPDATE TT
SET Col = CASE WHEN RN = 1 THEN 'Hello' END
FROM
(
  SELECT Col, ROW_NUMBER() OVER(PARTITION BY Col ORDER BY Col) RN
  FROM T
)TT;

Online Demo

SQL Server - updating distinct values

5 Answers5