Update column in SQL table with values from another row in same table

Question

I have a table with 5 columns (name, record, un, svun, svrecord):

Name	idrecord	un	svun	svrecord
John Doe	JD123	johndoe	NULL	JM123
Jane Doe	JaD123	janedoe	NULL	OR123
Olive Err	OR123	oliverr	NULL	GH123

I'm trying to populate the svun column with the value from the idrecord column when the svrecord matches an idrecord from another row.

For instance, row #2 should update the svun column to OR123 because the svrecord (OR123) matches the idrecord (OR123) from the table. Hope this makes sense.

I've started trying to come up with a query for this below but can't quite figure it out...I'm sure i'm missing a parameter to make this work or maybe an additional select statement...:

UPDATE table 
SET svun = idrecord 
WHERE (svrecord = idrecord)

"I have a table with 4 columns (name, record, un, svun)" Your sample data has five columns. — Thom A
– Thom A ♦, Commented Oct 11, 2022 at 15:35
Don't copy data around. Such behavior typically leads to data inconsistency. Create a view, or have a computed column. — jarlh
– jarlh, Commented Oct 11, 2022 at 15:41

Lajos Arpad · Accepted Answer · 2022-10-11 15:56:31Z

1

First, take a look at update joins here: https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/

You will see this example:

UPDATE 
    t1
SET 
    t1.c1 = t2.c2,
    t1.c2 = expression,
    ...   
FROM 
    t1
    [INNER | LEFT] JOIN t2 ON join_predicate
WHERE 
    where_predicate;

Now, we need to apply this for your table:

UPDATE
    t1
SET
    t1.svun = t1.idrecord
FROM
    yourtable t1
JOIN
    yourtable t2
ON
    t1.svrecord = t2.idrecort

answered Oct 11, 2022 at 15:56

Lajos Arpad

80.3k42 gold badges122 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simeon · Accepted Answer · 2022-10-11 15:46:47Z

1

Your initial query will only update svun = idrecord where svrecord = idrecord in the same row.

UPDATE table SET svun = idrecord WHERE (svrecord = idrecord)

In order to update records based on values that match in different rows you'll have to use a self join

with new_results as ( 
 select a.Name, a.svun, a.idrecord
 from table a
 inner join table b
 where a.svrecord = b.idrecord 
)

update new_results set svun = idrecord where svun is null

I don't recommend running this code without testing, but this should get you started

answered Oct 11, 2022 at 15:46

Simeon

8366 silver badges14 bronze badges

Comments

Willi Jakob · Accepted Answer · 2022-10-11 15:52:13Z

1

Update table1
SET table1.svun = table2.idrecord
FROM table table1
INNER JOIN table table2 
    ON table1.svrecord = table2.idrecord

answered Oct 11, 2022 at 15:52

Willi Jakob

394 bronze badges

Comments

MintBerryCRUNCH · Accepted Answer · 2022-10-11 15:55:31Z

1

You could join the table to itself.

UPDATE YourTable
SET a.svun = b.idrecord
FROM YourTable a 
INNER JOIN YourTable b
ON a.svrecord = b.idrecord

edited Oct 11, 2022 at 15:55

answered Oct 11, 2022 at 15:46

MintBerryCRUNCH

5617 silver badges26 bronze badges

Collectives™ on Stack Overflow

Update column in SQL table with values from another row in same table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related