MySQL - 在一个查询中更新具有不同值的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25674737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - UPDATE multiple rows with different values in one query
提问by franvergara66
I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.
我试图了解如何使用不同的值更新多行，但我不明白。解决方案无处不在，但对我来说似乎很难理解。
For instance, three updates into 1 query:
例如，三个更新为 1 个查询：
UPDATE table_users
SET cod_user = '622057'
, date = '12082014'
WHERE user_rol = 'student'
AND cod_office = '17389551';
UPDATE table_users
SET cod_user = '2913659'
, date = '12082014'
WHERE user_rol = 'assistant'
AND cod_office = '17389551';
UPDATE table_users
SET cod_user = '6160230'
, date = '12082014'
WHERE user_rol = 'admin'
AND cod_office = '17389551';
I readan example, but I really don't understand how to make the query. i.e:
我读了一个例子，但我真的不明白如何进行查询。IE：
UPDATE table_to_update
SET cod_user= IF(cod_office = '17389551','622057','2913659','6160230')
,date = IF(cod_office = '17389551','12082014')
WHERE ?? IN (??) ;
I'm not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?
如果 WHERE 和 IF 条件中有多个条件，我不完全清楚如何进行查询..有什么想法吗？
回答by Gordon Linoff
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.
我不明白你的日期格式。日期应使用本机日期和时间类型存储在数据库中。
回答by Trevedhek
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.
MySQL 允许以一种更具可读性的方式将多个更新组合到一个查询中。这似乎更适合您描述的场景，更容易阅读，并且避免了那些难以解开的多重条件。
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_officecombination 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.
这假设user_rol, cod_office组合是主键。如果其中只有一个是主键，则将另一个字段添加到 UPDATE 列表中。如果它们都不是主键（这似乎不太可能），那么这种方法将始终创建新记录 - 可能不是想要的。
However, this approach makes prepared statements easier to build and more concise.
但是，这种方法使准备好的语句更易于构建且更简洁。
回答by Hart CO
You can use a CASEstatement to handle multiple if/then scenarios:
您可以使用一个CASE语句来处理多个 if/then 场景：
UPDATE table_to_update
SET cod_user= CASE WHEN user_rol = 'student' THEN '622057'
WHEN user_rol = 'assistant' THEN '2913659'
WHEN user_rol = 'admin' THEN '6160230'
END
,date = '12082014'
WHERE user_rol IN ('student','assistant','admin')
AND cod_office = '17389551';
回答by Akshay Bhan
update table_name
set cod_user =
CASE
WHEN user_rol = 'student' THEN '622057'
WHEN user_rol = 'assistant' THEN '2913659'
WHEN user_rol = 'admin' THEN '6160230'?
END,date = '12082014'
WHERE user_rol IN ('student','assistant','admin')
AND cod_office = '17389551';
回答by Sab
To Extend on @Trevedhek answer,
要扩展@Trevedhek答案，
In case the update has to be done with non-unique keys, 4 queries will be need
如果必须使用非唯一键进行更新，则需要 4 次查询
NOTE: This is not transaction-safe
注意：这不是交易安全的
This can be done using a temp table.
这可以使用临时表来完成。
Step 1: Create a temp table keys and the columns you want to update
步骤 1：创建临时表键和要更新的列
CREATE TEMPORARY TABLE temp_table_users
(
cod_user varchar(50)
, date varchar(50)
, user_rol varchar(50)
, cod_office varchar(50)
) ENGINE=MEMORY
Step 2: Insert the values into the temp table
第 2 步：将值插入到临时表中
Step 3: Update the original table
第三步：更新原表
UPDATE table_users t1
JOIN temp_table_users tt1 using(user_rol,cod_office)
SET
t1.cod_office = tt1.cod_office
t1.date = tt1.date
Step 4: Drop the temp table
第 4 步：删除临时表
回答by ru51an
I did it this way:
我是这样做的：
<update id="updateSettings" parameterType="PushSettings">
<foreach collection="settings" item="setting">
UPDATE push_setting SET status = #{setting.status}
WHERE type = #{setting.type} AND user_id = #{userId};
</foreach>
</update>
where PushSettings is
PushSettings 在哪里
public class PushSettings {
private List<PushSetting> settings;
private String userId;
}
it works fine
它工作正常
回答by ankit giri
UPDATE Table1 SET col1= col2 FROM (SELECT col2, col3 FROM Table2) as newTbl WHERE col4= col3
Here col4 & col1 are in Table1. col2 & col3 are in Table2
I Am trying to update each col1 where col4 = col3 different value for each row
这里 col4 & col1 在 Table1 中。col2 & col3 在 Table2
我试图更新每个 col1 其中 col4 = col3 每行的不同值
