mysql在同一表的UPDATE中嵌套了SELECT [英] mysql nested SELECT in UPDATE of same table

问题描述

本质上，我需要执行以下操作....这只是一个示例...但是第一个查询的语法在MySQL中不起作用

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

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>;

在任何课程中，只有一个人可以获得金奖.我只知道获得金奖的人的person_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.

我相信我需要使用某种类型的内部联接，但是对此我不是100％肯定.

I believe I need to use some type of inner join, but I'm not 100% sure on this.

如果我可以在一个查询中完成全部任务，那会更聪明！

What would be even smarter if I could do the whole lot in one query!

任何人都可以提供建议吗?

Can anyone lend advice?

谢谢:)

推荐答案

UPDATE people
SET    prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' END
WHERE  class = (
       SELECT  class
       FROM    people
       WHERE   id = @lucky
       )

这将授予所有获得Silver奖励的用户，除了@lucky获得Gold的用户.

This will grant all users with a Silver prize, except the @lucky one who gets the Gold.

如果只需要更新@lucky和ex-champion，请发出以下命令:

If you only need to update the @lucky and the ex-champion, issue the following:

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
       )

这篇关于mysql在同一表的UPDATE中嵌套了SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！