Update values in one column based on another column

Question

I would like to update USER_FNM in temporary table @Users with values from table BAUSER.

Table @Users:

DECLARE @USERS TABLE
(
  USER_KEY INT,
  USER_FNM VARCHAR(50),
  USER_LNM VARCHAR(50),
  USDE_HSU DECIMAL(8,2),
  [110000003] DECIMAL(8,2),
[120000002] DECIMAL(8,2),
[120000003] DECIMAL(8,2)
)

What I've already tried is this:

update @USERS 
set USER_FNM = (select top(1) user_fnm from bauser where bauser.user_key = USER_KEY)

But this always return the first value from BAUSER table. How to iterate over each row of @Users and change USER_FNM?

There are multiple examples on the [MSDN article](https://msdn.microsoft.com/en-us/library/ms177523.aspx) for the `UPDATE` statement. — Taylor Buchanan, Feb 15 '17 at 15:28
Because a simple Google search of 'sql update from another table' yields various sources you could have used. — Taylor Buchanan, Feb 16 '17 at 14:12
Possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Taylor Buchanan, Feb 16 '17 at 14:13

score 2 · Accepted Answer · answered Feb 15 '17 at 10:38

2

SQL Server supports joins in update statements.
Assuming user_key is unique in table bauser, you can simply do this:

UPDATE t 
SET USER_FNM = bauser.user_fnm 
FROM @Users t
INNER JOIN bauser ON bauser.user_key = t.USER_KEY

answered Feb 15 '17 at 10:38

Zohar Peled

79,642
10
69
121

Update values in one column based on another column

1 Answers1