CODEWITHSUNDEEP
sqlsql-server
bstricks
bstricks

Reputation: 923

How to update a column of every row in a sql table with the value of that column from another specific row in the same table

So I have a table called dummy that looks like

A | B | C | D | E
------------------
1 | 2 | 3 | 4 | u1
1 | 2 | 4 | 3 | u2
1 | 2 | 4 | 3 | u3

I've decided that every row needs to have the same C column WHERE E = u1. So I would end up with:

A | B | C | D | E
------------------
1 | 2 | 3 | 4 | u1
1 | 2 | 3 | 3 | u2
1 | 2 | 3 | 3 | u3

To do this I ran the following query:

update tb1 
set tb1.C = tb2.C
from dummy as tb1, dummy as tb2
where tb2.E = 'u1'

I was just curious if this is the best way to do something like this in SQL Server.

Upvotes: 2

Views: 2269

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

;WITH x as
(
  SELECT e,c, MAX(CASE WHEN E = 'u1' THEN c END) OVER () newc
  FROM tb1
)
UPDATE x 
SET c = newc
WHERE e <> 'u1'

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

Test Data

DECLARE @TABLE TABLE (A INT,B INT,C INT,D INT,E VARCHAR(2))
INSERT INTO @TABLE VALUES
(1,2,3,4,'u1'),(1,2,4,3,'u2'),(1,2,4,3,'u3')

Update Query

UPDATE @TABLE 
SET C = (SELECT TOP 1 C 
         FROM @TABLE
         WHERE E = 'u1')

Result Set

SELECT * FROM @TABLE

╔═══╦═══╦═══╦═══╦════╗
║ A ║ B ║ C ║ D ║ E  ║
╠═══╬═══╬═══╬═══╬════╣
║ 1 ║ 2 ║ 3 ║ 4 ║ u1 ║
║ 1 ║ 2 ║ 3 ║ 3 ║ u2 ║
║ 1 ║ 2 ║ 3 ║ 3 ║ u3 ║
╚═══╩═══╩═══╩═══╩════╝

Upvotes: 1

valex
valex

Reputation: 24134

Try this

update dummy set C = (SELECT TOP 1 C FROM  dummy WHERE E = 'u1')

Upvotes: 0

Related Questions