Update Multiple Columns in SQL

Update multiple columns in SQL

The "tiresome way" is standard SQL and how mainstream RDBMS do it.

With a 100+ columns, you mostly likely have a design problem... also, there are mitigating methods in client tools (eg generation UPDATE statements) or by using ORMs

How to update multiple columns in single update statement in DB2

The update statement in all versions of SQL looks like:

update table
    set col1 = expr1,
        col2 = expr2,
        . . .
        coln = exprn
    where some condition

So, the answer is that you separate the assignments using commas and don't repeat the set statement.

How to update multiple columns having the same value or different values in SQL?

You can, by using a case expression, but whats the advantage?

update table_name set
  col1 = case when col1 = -99 then null /* or any new value for col1 */ else col1 end
  , col2 = case when col2 = -99 then null /* or any new value for col2 */ else col2 end
where col1 = -99 or col2 = -99;

Note, as pointed out by Larnu, when you are setting the column to null you can simplify the update to:

update table_name set
  col1 = nullif(col1,-99)
  , col2 = nullif(col2,-99)
where col1 = -99 or col2 = -99;

And you can change the values you are using (-99) on a per column basis to whatever you want e.g. col2 = 5

update table_name set
  col1 = nullif(col1,-99)
  , col2 = nullif(col2,5)
where col1 = -99 or col2 = 5;

Update multiple columns of table based on another table sql

The exakt SQL command will of course depend on the query which produces your second table. Assuming, this query would just be "SELECT * FROM yourtable2", you can do following update command to achieve your goal:

UPDATE yourtable 
SET 
column2 = x.column2, 
column4 = x.column4
FROM (SELECT * FROM yourtable2) x
WHERE yourtable.column1 = x.column1
AND yourtable.column5 = x.column5

Here you see this is working (in case the table "yourtable2" provides the correct data): db<>fiddle
So, you can replace the "SELECT FROM yourtable2" by your query and it will work.

How to update multiple columns in SQL Server?

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.

Update multiple columns in table with SET command

update pipe_tally3
set cuts = s.cuts, new_lt = lt - s.cuts
from (
    select 2.0 as cuts
) s
where jt_id = 'asset-33';

Update multiple columns from a sub query

update PINPOINT_SUPPLEMENT
set
    ATTACHMENT_VALUE = PINPOINT_DOCUMENT.key,
    ATTACHMENT_TYPE = 'file'
from PINPOINT_DOCUMENT
where
    PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE::integer = PINPOINT_DOCUMENT.DOCUMENT_ID
    and PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE IS NULL

or

update PINPOINT_SUPPLEMENT
set
    (ATTACHMENT_VALUE,ATTACHMENT_TYPE) = (PINPOINT_DOCUMENT.key, 'file')
from PINPOINT_DOCUMENT
where
    PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE::integer = PINPOINT_DOCUMENT.DOCUMENT_ID
    and PINPOINT_SUPPLEMENT.ATTACHMENT_VALUE IS NULL

---

Related Topics