Amit Samnani Amit Samnani - 1 year ago 126
SQL Question

How to update multiple columns in SQL Server?

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';

Answer Source

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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download