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.
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
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.
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.
Multiple SQL Update Statements in single query
Yes, you could add all the single-line-Update-statements in one query like you are doing.
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
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.
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
What Is a 'Multi-Part Identifier' and Why Can't It Be Bound
Huge Performance Difference When Using Group by VS Distinct
Subquery in from Must Have an Alias
Best Way to Count Rows by Arbitrary Time Intervals
Postgresql Sequence Based on Another Column
Return a Value If No Record Is Found
How to Send Email from Postgresql Trigger
Constraint for Only One Record Marked as Default
Postgres Window Function and Group by Exception
Protecting Against SQL Injection in Python
Are a Case Statement and a Decode Equivalent
Linq Version of SQL "In" Statement
How to Store Historical Records in a History Table in SQL Server
Postgresql Delete with Inner Join
How to Pass Parameters to a View in SQL