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

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)

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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