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.
Answer from Gordon Linoff on Stack OverflowYou 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.
MySQL allows a more readable way to combine multiple updates into a single query. This seems to better fit the scenario you describe, is much easier to read, and avoids those difficult-to-untangle multiple conditions.
INSERT INTO table_users (cod_user, date, user_rol, cod_office)
VALUES
('622057', '12082014', 'student', '17389551'),
('2913659', '12082014', 'assistant','17389551'),
('6160230', '12082014', 'admin', '17389551')
ON DUPLICATE KEY UPDATE
cod_user=VALUES(cod_user), date=VALUES(date)
This assumes that the user_rol, cod_office combination is a primary key. If only one of these is the primary key, then add the other field to the UPDATE list.
If neither of them is a primary key (that seems unlikely) then this approach will always create new records - probably not what is wanted.
However, this approach makes prepared statements easier to build and more concise.
How To update multiple rows at the same time in SQL
sqlite - Update multiple rows with different values in a single SQL query - Stack Overflow
mysql - Updating multiple rows with different values in one query - Database Administrators Stack Exchange
mysql - SQL - Update multiple records in one query - Stack Overflow
Videos
| Id | Name | Price |
|---|---|---|
| 1 | Pen | 50 |
| 2 | Pencil | 60 |
This is my table structure now I want to update the price of Pen and Pencil at the same time with one SQL query
There's a couple of ways to accomplish this decently efficiently.
First -
If possible, you can do some sort of bulk insert to a temporary table. This depends somewhat on your RDBMS/host language, but at worst this can be accomplished with a simple dynamic SQL (using a VALUES() clause), and then a standard update-from-another-table. Most systems provide utilities for bulk load, though
Second -
And this is somewhat RDBMS dependent as well, you could construct a dynamic update statement. In this case, where the VALUES(...) clause inside the CTE has been created on-the-fly:
WITH Tmp(id, px, py) AS (VALUES(id1, newsPosX1, newPosY1),
(id2, newsPosX2, newPosY2),
......................... ,
(idN, newsPosXN, newPosYN))
UPDATE TableToUpdate SET posX = (SELECT px
FROM Tmp
WHERE TableToUpdate.id = Tmp.id),
posY = (SELECT py
FROM Tmp
WHERE TableToUpdate.id = Tmp.id)
WHERE id IN (SELECT id
FROM Tmp)
(According to the documentation, this should be valid SQLite syntax, but I can't get it to work in a fiddle)
One way: SET x=CASE..END (any SQL)
Yes, you can do this, but I doubt that it would improve performances, unless your query has a real large latency.
If the query is indexed on the search value (e.g. if id is the primary key), then locating the desired tuple is very, very fast and after the first query the table will be held in memory.
So, multiple UPDATEs in this case aren't all that bad.
If, on the other hand, the condition requires a full table scan, and even worse, the table's memory impact is significant, then having a single complex query will be better, even if evaluating the UPDATE is more expensive than a simple UPDATE (which gets internally optimized).
In this latter case, you could do:
UPDATE table SET posX=CASE
WHEN id=id[1] THEN posX[1]
WHEN id=id[2] THEN posX[2]
...
ELSE posX END [, posY = CASE ... END]
WHERE id IN (id[1], id[2], id[3]...);
The total cost is given more or less by: NUM_QUERIES * ( COST_QUERY_SETUP + COST_QUERY_PERFORMANCE ). This way, you knock down on NUM_QUERIES (from N separate id's to 1), but COST_QUERY_PERFORMANCE goes up (about 3x in MySQL 5.28; haven't yet tested in MySQL 8).
Otherwise, I'd try with indexing on id, or modifying the architecture.
This is an example with PHP, where I suppose we have a condition that already requires a full table scan, and which I can use as a key:
// Multiple update rules
$updates = [
"fldA='01' AND fldB='X'" => [ 'fldC' => 12, 'fldD' => 15 ],
"fldA='02' AND fldB='X'" => [ 'fldC' => 60, 'fldD' => 15 ],
...
];
The fields updated in the right hand expressions can be one or many, must always be the same (always fldC and fldD in this case). This restriction can be removed, but it would require a modified algorithm.
I can then build the single query through a loop:
$where = [ ];
$set = [ ];
foreach ($updates as $when => $then) {
$where[] = "({$when})";
foreach ($then as 
value) {
if (!array_key_exists(
set)) {
fld] = [ ];
}
fld][] = $value;
}
}
$set1 = [ ];
foreach (
fld => $values) {
$set2 = "{$fld} = CASE";
foreach ($values as 
value) {
$set2 .= " WHEN {$where[$i]} THEN {$value}";
}
$set2 .= ' END';
$set1[] = $set2;
}
// Single query
$sql = 'UPDATE table SET '
. implode(', ', $set1)
. ' WHERE '
. implode(' OR ', $where);
Another way: ON DUPLICATE KEY UPDATE (MySQL)
In MySQL I think you could do this more easily with a multiple INSERT ON DUPLICATE KEY UPDATE, assuming that id is a primary key keeping in mind that nonexistent conditions ("id = 777" with no 777) will get inserted in the table and maybe cause an error if, for example, other required columns (declared NOT NULL) aren't specified in the query:
INSERT INTO tbl (id, posx, posy, bazinga)
VALUES (id1, posY1, posY1, 'DELETE'),
...
ON DUPLICATE KEY SET posx=VALUES(posx), posy=VALUES(posy);
DELETE FROM tbl WHERE bazinga='DELETE';
The 'bazinga' trick above allows to delete any rows that might have been unwittingly inserted because their id was not present (in other scenarios you might want the inserted rows to stay, though).
For example, a periodic update from a set of gathered sensors, but some sensors might not have been transmitted:
INSERT INTO monitor (id, value)
VALUES (sensor1, value1), (sensor2, 'N/A'), ...
ON DUPLICATE KEY UPDATE value=VALUES(value), reading=NOW();
(This is a contrived case, it would probably be more reasonable to LOCK the table, UPDATE all sensors to N/A and NOW(), then proceed with INSERTing only those values we do have).
A third way: CTE (Any SQL)
This is conceptually almost the same as the INSERT MySQL trick. As written, it works in PostgreSQL 9.6:
WITH updated(id, posX, posY) AS (VALUES
(id1, posX1, posY1),
(id2, posX2, posY2),
...
)
UPDATE myTable
SET
posX = updated.posY,
posY = updated.posY
FROM updated
WHERE (myTable.id = updated.id);
UPDATE mytable SET
fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END,
drink = CASE WHEN id=1 THEN 'water' ELSE 'wine' END,
food = CASE WHEN id=1 THEN 'pizza' ELSE 'fish' END
WHERE id IN (1,2);
Personally, using CASE WHEN THEN END looks clumsy.
You could code this using the IF function.
UPDATE mytable SET
fruit = IF(id=1,'orange','strawberry'),
drink = IF(id=1,'water','wine'),
food = IF(id=1,'pizza','fish')
WHERE id IN (1,2);
Give it a Try !!!
CAVEAT : CASE WHEN THEN END is only handy when dealing with multiple values (more than 2)
INSERT ... ON DUPLICATE KEY UPDATE
You will need to write very complicated conditions if you want to update more than two rows. In such a case you can use INSERT ... ON DUPLICATE KEY UPDATE approach.
INSERT into `mytable` (id, fruit, drink, food)
VALUES
(1, 'orange', 'water', 'pizza'),
(2, 'strawberry', 'wine', 'fish'),
(3, 'peach', 'jiuce', 'cake')
ON DUPLICATE KEY UPDATE
fruit = VALUES(fruit),
drink = VALUES(drink),
food = VALUES(food);
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
You can accomplish it with INSERT as below:
INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c);
This insert new values into table, but if primary key is duplicated (already inserted into table) that values you specify would be updated and same record would not be inserted second time.
Hi @vy ，
Welcome to the Microsoft SQL Server Q&A Forum！
Regarding your question, I checked some documents.The conclusion drawn from my own perspective is as follows:
When describing the contents of a table, most people usually display the rows in a specific order. But the table actually represents a collection, and the collection has no order. (Tables with clustered index added are stored in the order of the clustered index columns).
The virtual table(there is no order like a normal table) returned by the inner join operator is as follows：
id color id color
1 NULL 1 red
1 NULL 1 blue
The update statement returns a row from the virtual table to update the t1.color column. The column that satisfies the condition t1.id = c.id has two columns. It is uncertain which column is returned.SQL Server will return the row that happened to be accessed first.Therefore, different results may be produced, but they can all be considered correct. If you want to ensure the certainty of the results, you can choose to include a unique order by list.
Take the select statement as an example:
select top(1) *
from c
order by color
The above select statement specifies a unique order by list (the color field is unique), so the returned result is certain.
The result returned by the following statement is not certain (of course they are all considered correct)：
select top(1) *
from c
order by id
select top(1) *
from c
In short, SQL Server will return the row that happens to be accessed first, and which row is accessed first is up to the developer.
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
It's not deterministic. Tom said "random", but that's inexact. As long as the query plan is the same, it is very likely that you will get the same value every time. At least if the plan is serial. But if the plan changes - you could also get a different value.
The important thing is that you cannot rely on anything here. Not even the value being randomly chosen.
UPDATE T
SET Size = CASE SKU
WHEN 'A' THEN 20
WHEN 'B' THEN 10
WHEN 'C' THEN 30
WHEN ...
END
Or there may be a formula for calculating the size, but you've failed to give it in your question (Or we may have to switch to a more complex CASE expression, but again, too little detail in the question).
Create a table with the mapping of SKU to new size; update the master table from that.
Many dialects of SQL have a notation for doing updates via joined tables. Some do not. This will work where there is no such notation:
CREATE TABLE SKU_Size_Map
(
SKU CHAR(16) NOT NULL,
Size INTEGER NOT NULL
);
...Populate this table with the SKU values to be set...
...You must have such a list...
UPDATE MasterTable
SET Size = (SELECT Size FROM SKU_Size_Map
WHERE MasterTable.SKU = SKU_Size_Map.Size)
WHERE SKU IN (SELECT SKU FROM SKU_Size_Map);
The main WHERE condition is need to avoid setting the size to null where there is no matching row.
You can probably also do it with a MERGE statement. But the key insight for any of these notations is that you need a table to do the mapping between SKU and size. You either need a table or you need an algorithm, and the sample data doesn't suggest an algorithm.
Remarks
It is possible to update rows based on some condition. It is also possible to update multiple tables in one statement in MySQL.
Whether the latter is a good idea is debatable, though. The target tables would be joined together for the update, and when I say "joined", I mean it in a broader sense: you do not have to specify a joining condition, in which case theirs would be a cross join. In a cross join, when at least one of the tables has more than one row, the other table will inevitably have its rows duplicated in the joined set. If both have multiple rows, both will have them multiplied. Somewhat counter-intuitively, MySQL will still update each affected row just once, yet I would refrain from multi-table updates in such scenarios, even if solely because of the counter-intuitiveness.
Method 1
Anyway, moving on to your specific example, there is indeed no joining condition, only a filter on each table. You can specify those filters in the WHERE clause of the UPDATE. Now in order to select which value to update each column with, you can use a CASE expression. This is what the complete UPDATE statement might look like:
UPDATE
A, B
SET
A.col1 = 'abc',
A.col2 = 'xyz',
B.col1 = CASE B.col3
WHEN '1' THEN 'a'
WHEN '2' THEN 'b'
WHEN '3' THEN 'c'
END,
B.col2 = CASE B.col3
WHEN '1' THEN 'x'
WHEN '2' THEN 'y'
WHEN '3' THEN 'z'
END
WHERE
A.col3 = '1'
AND B.col3 IN ('1', '2', '3')
;
You can see that you have to repeat the same set of conditions in a CASE expression both for B.col1 and for B.col2. Is there a way to avoid that?
Method 2
Yes, there is. You can arrange the target values for B.col1 and B.col2 as well as the filtering values for B.col3 as a derived table and join it to B in the UPDATE clause, like this:
UPDATE
A,
B
INNER JOIN
(
SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
UNION ALL
SELECT 'b', 'y', '2'
UNION ALL
SELECT 'c', 'z', '3'
) AS fltr ON B.col3 = fltr.col3
SET
A.col1 = 'abc',
A.col2 = 'xyz',
B.col1 = fltr.col1,
B.col2 = fltr.col2
WHERE
A.col3 = '1'
;
The join is also acting as a filter for B, so you can omit the one in the WHERE clause.
You can find a demo for each method at 
db<>fiddle:
- Method 1
- Method 2
Better way
Finally, as have been remarked both at the beginning of this post and in the comments, you can have a separate UPDATE statement for each table. The result would be clear in intention both to the reader of your script and to the database engine. A simpler script enables the latter to have more options for optimisation.
Use either of the methods above for the table B update, but do both tables separately:
UPDATE
A
SET
A.col1 = 'abc',
A.col2 = 'xyz'
WHERE
A.col3 = '1'
;
UPDATE
B
INNER JOIN
(
SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
UNION ALL
SELECT 'b', 'y', '2'
UNION ALL
SELECT 'c', 'z', '3'
) AS fltr ON B.col3 = fltr.col3
SET
B.col1 = fltr.col1,
B.col2 = fltr.col2
;
There is also another reason for splitting the updates in separate statements. Since for a single UPDATE statement the tables need to be joined, it is important that both tables have rows intended for the update. If one table has no matching rows, then, even if the other does, neither will be updated. This is because an empty set cross-joined to a non-empty set still results in an empty set.
So, the single UPDATE statement would have no rows to work with if at least one table had no rows matching the condition(s). That would not happen with separate UPDATEs, because each would work with its own table regardless of the contents of the other, therefore, absence of rows in one table would not affect the update of the other.
You can use below one for one table if you want to update many columns of one table.
UPDATE table
SET col1 = CASE WHEN col3 = 'name1' THEN 'a'
WHEN col3 = '2' THEN b
ELSE 0
END
, col2 = CASE WHEN col3 = '1' THEN 'b'
WHEN col3 = 'name2' THEN 'c'
ELSE ''
END
;