Multiple SQL Update Statements in Single Query

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

UPDATE multiple rows with different values in one query in MySQL

You can do it this way:

UPDATE table_users
    SET cod_user = (case when user_role = 'student' then '622057'
                         when user_role = 'assistant' then '2913659'
                         when user_role = 'admin' then '6160230'
                    end),
        date = '12082014'
    WHERE user_role in ('student', 'assistant', 'admin') AND
          cod_office = '17389551';

I don't understand your date format. Dates should be stored in the database using native date and time types.

Multiple SQL Update Statements in single query

Yes, you could add all the single-line-Update-statements in one query like you are doing.

Multiple SET in single UPDATE statement?

To have one update, you can use two LEFT JOINs, one for each table join condition. And then, in SET part, you use CASE WHEN ... THEN ... END checking if the PK from related joins IS NULL.

Something like below

UPDATE  TABLE1
SET COL1=CASE 
     WHEN T1.PID1 IS NOT NULL THEN T1.ATTRIBUTE1 
     WHEN T2.PID1 IS NULL THEN ’123-4567890-1’
     ELSE COL1
    END,

    etc.

FROM TABLE1
    LEFT JOIN TABLE2 T1 ON COL1=T1.PID1 AND COL2=T1.PID2 AND ROWNUM < 10
    LEFT JOIN TABLE2 T2 ON CEDULA=T2.PID1

WHERE T2.PID1 IS NULL OR T1.PID1 IS NOT NULL

However, having one UPDATE statement doesn't mean it will be faster - check the query plan and actual performance.

Is it possible to perform multiple updates with a single UPDATE SQL statement?

In a more general case, where there could be many hundreds of mappings to each of the new values, you would create a separate table of the old and new values, and then use that in the UPDATE statement. In one dialect of SQL:

CREATE TEMP TABLE mapper (old_val CHAR(5) NOT NULL, new_val CHAR(5) NOT NULL);
...multiple inserts into mapper...
INSERT INTO mapper(old_val, new_val) VALUES('a.1', 'a1');
INSERT INTO mapper(old_val, new_val) VALUES('a-1', 'a1');
INSERT INTO mapper(old_val, new_val) VALUES('b.1', 'b1');
INSERT INTO mapper(old_val, new_val) VALUES('b-1', 'b1');
...etcetera...

UPDATE tbl
   SET title = (SELECT new_val FROM mapper WHERE old_val = tbl.title)
   WHERE title IN (SELECT old_val FROM mapper);

Both select statements are crucial. The first is a correlated sub-query (not necessarily fast, but faster than most of the alternatives if the mapper table has thousands of rows) that pulls the new value out of the mapping table that corresponds to the old value. The second ensures that only those rows which have a value in the mapping table are modified; this is crucial as otherwise, the title will be set to null for those rows without a mapping entry (and those were the records that were OK before you started out).

For a few alternatives, the CASE operations are OK. But if you have hundreds or thousands or millions of mappings to perform, then you are likely to exceed the limits of the SQL statement length in your DBMS.

Is there a way to merge multiple update queries into one query?

You could use CASE:

update Provinces
set Description= CASE code WHEN 'MS' THEN 'Mississippi'
                           WHEN 'MD' THEN 'Maryland' 
                           -- ELSE 'some default value' --otherwise NULL
                 END
where Code IN('MS', 'MD');

---

Another approach is to create table variable and use UPDATE FROM JOIN syntax:

DECLARE @t AS (code VARCHAR(10), desc VARCHAR(1000));
INSERT INTO @t(code, desc) VALUES ('MS','Mississippi'), ('MD', 'Maryland');

UPDATE p
SET desc = t.desc
FROM Provinces p
JOIN @t t
  ON p.Code = t.code;

SQL Server : when using multiple UPDATES in a single query, will the WHERE clause use the current or updated field values?

This is a manifestation of the Halloween problem.
You could also get constraint errors because of how updates are applied (especially on uniqueness)

The way to do many updates in one go to avoid intermediate errors is to do a single update

UPDATE
    A
SET
    A.AssetID = N.NewAssetID
FROM
   Asset A
   JOIN
   SomeTempTableOrTableVar N ON A.AssetID = OldAssetId 

SomeTempTableOrTableVar is a tablevalued parameter, temp table or table variable of values clause with 2 columns

Edit: Or use an extra column as per OP's comment.

Example

UPDATE Asset SET AssetID = '001' WHERE AssetID = '111'
UPDATE Asset SET AssetID = '002' WHERE AssetID = '001'
UPDATE Asset SET AssetID = '003' WHERE AssetID = '002'

...becomes this:

UPDATE
    A
SET
    A.AssetID = N.NewAssetID
FROM
   Asset A
   JOIN
   (VALUES
      ('111', '001'),
      ('001', '002'),
      ('002', '003')
   ) N (OldAssetId, NewAssetID) ON A.AssetID = OldAssetId 

SQL Multiple Updates vs single Update performance

SQL is supposed to be a declarative language; it does not expect from the user to say "how" to get the result, only "what" the desired result is. So in principal I would use the in() construct, as this is the most concise (from a logical viewpoint) way to ask for the results, and let the DBMS (any DBMS!) decide what's best.

SQL Statement with multiple SETs and WHEREs

NO!

You'll need to handle those individually

Update [table]
Set ID = 111111259
WHERE ID = 2555

Update [table]
Set ID = 111111261
WHERE ID = 2724

--...

---

Related Topics