1

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DS123
  • 11
  • 2

5 Answers5

2

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 |
D-Shih
  • 44,943
  • 6
  • 31
  • 51
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)
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
0

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

UPDATE tablename SET column2 = 'Hello' WHERE column3 % 2 != 0
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
0

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
sacse
  • 3,634
  • 2
  • 15
  • 24
0

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

Ilyes
  • 14,640
  • 4
  • 29
  • 55