mysql 在同一表的 UPDATE 中嵌套 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/916789/
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 nested SELECT in UPDATE of same table
提问by adam
Essentially I need to do something like this.... this is just an example... but the syntax of the first query doesn't work in MySQL
基本上我需要做这样的事情......这只是一个例子......但第一个查询的语法在MySQL中不起作用
update people set prize = ''
where prize = 'Gold' and class = (select class from people where id = person_id);
update people set prize = 'Gold' where id = <id>;
Only one person can have the Gold prize in any class. I only know the person_id of the person who receives the Gold prize.
在任何课程中，只有一个人可以获得金奖。我只知道金奖获得者的person_id。
I am trying to blank out any previous Gold prize winners in the same class as person_id in the first query. Then set the new Gold winner in the second.
我试图在第一个查询中清除与 person_id 同一班级的任何以前的金奖得主。然后在第二个中设置新的金牌得主。
I believe I need to use some type of inner join, but I'm not 100% sure on this.
我相信我需要使用某种类型的内部连接，但我不是 100% 确定这一点。
What would be even smarter if I could do the whole lot in one query!
如果我可以在一个查询中完成所有工作，那会更聪明！
Can anyone lend advice?
任何人都可以提供建议吗？
Thanks :)
谢谢 ：）
采纳答案by Quassnoi
UPDATE people
SET prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' END
WHERE class = (
SELECT class
FROM people
WHERE id = @lucky
)
This will grant all users with a Silverprize, except the @luckyone who gets the Gold.
这将授予所有用户Silver奖品，除了@lucky获得Gold.
If you only need to update the @luckyand the ex-champion, issue the following:
如果您只需要更新@lucky和前冠军，请发出以下命令：
UPDATE people
SET prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' END
WHERE id = @lucky
OR (class, prize) =
(
SELECT class, 'Gold'
FROM people
WHERE id = @lucky
)
回答by Bill Karwin
It's not always best to do complex updates in a single SQL query. In fact, it could be more efficient to run two simple queries. So be sure to benchmark both solutions.
在单个 SQL 查询中进行复杂的更新并不总是最好的。事实上，运行两个简单的查询可能会更有效率。所以一定要对这两种解决方案进行基准测试。
MySQL supports an extension to UPDATEsyntax for multi-table update. You can perform a JOINas part of the update instead of using subqueries.
MySQL 支持UPDATE多表更新语法的扩展。您可以将 aJOIN作为更新的一部分执行，而不是使用子查询。
Then you can use the IF()function (or CASE) to change the prizevalue to different values conditionally.
然后您可以使用IF()函数（或CASE）prize有条件地将值更改为不同的值。
So if you absolutely must use a single query, try something like this:
因此，如果您绝对必须使用单个查询，请尝试以下操作：
UPDATE people p1 JOIN people p2
ON (p1.class = p2.class AND p2.id = <person_id>)
SET prize = IF(p1.id = <person_id>, 'Gold', '')
WHERE p1.id = <person_id> OR p1.prize = 'Gold';
Or this alternative:
或者这个替代方案：
UPDATE people p1 JOIN people p2
ON (p1.class = p2.class AND p2.id = <person_id>)
SET p1.prize = CASE
WHEN p1.id = <person_id> THEN 'Gold'
WHEN p1.prize = 'Gold' THEN ''
ELSE p1.prize -- other cases are left as is
END CASE;
回答by Chris Cameron-Mills
If you are after a single statement you could use the CASE operator? Not used MySQL but something like:
如果您在单个语句之后，您可以使用 CASE 运算符吗？未使用 MySQL，但类似于：
UPDATE people
SET prize = CASE
WHEN 'Gold' AND id != @newid THEN ''
WHEN '' AND id = @newid THEN 'Gold'
END
WHERE class = (
SELECT class
FROM people
WHERE id = @newid
)
回答by VVS
Since you're doing two distinct updates there's no need to do it in one query. If you're afraid of inconsistent data because one of the two queries might fail, you can use transactions and rollback of any of the two queries fails.
由于您正在执行两个不同的更新，因此无需在一个查询中进行。如果您担心数据不一致，因为两个查询中的一个可能会失败，您可以使用事务并且回滚两个查询中的任何一个失败。
Your current query is totally readable and understandable while the posted solutions are far from easy readable.
您当前的查询是完全可读和可理解的，而发布的解决方案远非易读。
回答by Travis
You could definitely create a stored procedure that takes the id of the new prize winner as an argument.
您绝对可以创建一个以新获奖者的 id 作为参数的存储过程。
Then you can call that stored procedure in one line.
然后您可以在一行中调用该存储过程。
I'm using MS SQL here but something similar to:
我在这里使用 MS SQL，但类似于：
CREATE PROC UpdatePrize
@newid int
AS
UPDATE people
SET prize = ''
WHERE prize = 'Gold'
AND class = ( SELECT class
FROM people
WHERE id = @newid )
UPDATE people
SET prize = 'Gold'
WHERE id = @newid
GO
