How to update multiple columns in SQL Server?

Question

I am trying to update multiple columns with a single query

This is my code: I want to update the

Employee

table and the table has multiple null values in

EmployeeCode

column that I have to update with new value.

update Employee 

set EmployeeCode = 26589, EmployeeCode = 26587 

where EmployeeID = 'EA45AED9-94A6-E711-AF12-E4029B75E01C' 

  and EmployeeID = '0A362F00-96A6-E711-AF12-E4029B75E01C';

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

If you really need to, you can do it in one step, using CTE:

WITH Pairs (code, id)
AS (
  SELECT 26589 AS code, 'EA45AED9-94A6-E711-AF12-E4029B75E01C' AS id
  UNION ALL
  SELECT 26587 AS code, '0A362F00-96A6-E711-AF12-E4029B75E01C' AS id
)
UPDATE Employee set EmployeeID = Pairs.id
FROM Pairs
INNER JOIN Employee ON (pairs.code = Employee .EmployeeCode)

Explanation:

the WITH Pairs (...) AS (...) part defines the code - id pairs you need. You can use as many SELECT -s here, as you need. Just keep UNION -ing them.
After the WITH you can update your Employee table. All you need to do is to do an INNER JOIN on your Pairs subquery. So you'll update only those rows that `you want to.

How to update multiple columns in SQL Server?

Sign up