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.
mysql - Updating multiple rows with different values in one query - Database Administrators Stack Exchange
Multiple rows updation in sql server
Update multiple rows with different values in SQL - Stack Overflow
Updating multiple rows with different values – SQLServerCentral Forums
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
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);
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.
Couldn't find a SQL Server 2008 fiddle engine so I had to opt for a SQL Server 2014 ... so not sure if the following will work in SQL Server 2008, but fwiw ...
Setup some sample data:
create table Table1(id int, Date datetime null);
create table Table2(id int, Date datetime);
insert Table1 values (1,null)
insert Table1 values (1,null)
insert Table1 values (2,null)
insert Table1 values (2,null)
insert Table1 values (2,null);
insert Table2 values (1,'2013-01-29 08:50:00.000')
insert Table2 values (1,'2013-01-29 15:28:00.000')
insert Table2 values (2,'2013-01-31 11:56:00.000')
insert Table2 values (2,'2013-03-11 16:08:00.000')
insert Table2 values (2,'2013-01-31 14:04:00.000');
Keeping in mind that we haven't been provided (yet) with any means to determine which rows to match between Table1 and Table2 for a given id value, I'll just let row_number() generate a 'matching' rowid.
And then we'll make use of SQL Server's ability to update Table1 via a derived table definition:
update T1
set T1.Date=T2.Date
from (select row_number() over(partition by id order by Date) as rowid,
id,
Date
from Table1
where Date is NULL) T1
join (select row_number() over(partition by id order by Date) as rowid,
id,
Date
from Table2) T2
on T1.id = T2.id
and T1.rowid = T2.rowid;
And the results:
select * from Table1;
id Date
--- --------------------
1 2013-01-29T08:50:00Z
1 2013-01-29T15:28:00Z
2 2013-01-31T11:56:00Z
2 2013-01-31T14:04:00Z
2 2013-03-11T16:08:00Z
And here's a SQL Fiddle for the above.
You stated that the order of the matching matters but it seems like you don't have anything to ORDER BY in table 1 to create a guaranteed order to match the other table and there is no way in SQL Server to order the rows after insertion date, because information about that is not stored. With this in mind it’s not possible to do a matching with the result you want. There is a solution to update the rows with an arbitrary match within each id. If that would be good enough.
UPDATE t
SET t.[date] = tt.[date]
FROM (SELECT *,
Row_number()
OVER (
partition BY id
ORDER BY [date]) AS rno
FROM Table1) AS t
INNER JOIN (SELECT *,
Row_number()
OVER (
partition BY id
ORDER BY [date]) AS rno
FROM Table2) AS tt
ON t.id = tt.id
AND t.rno = tt.rno
This solution will match all rows individually but can't guarantee the order.
DB Fiddle