代码之家 › 专栏 › 技术社区 › adam

mysql嵌套选择更新同一个表

join mysql sql

adam · 技术社区 · 15 年前

基本上我需要做一些像这样的事情。。。。这只是一个例子。。。但是第一个查询的语法在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在同一个类中的任何先前的金奖得主都删除掉。然后让新的金牌得主进入第二局。

我相信我需要使用某种类型的内部连接,但我不是100%确定这一点。

如果我能在一次查询中完成全部任务,那还有什么比这更聪明呢!

有人能提供建议吗?

谢谢:)

5 回复 | 直到 15 年前

Bill Karwin 15 年前

在单个SQL查询中执行复杂的更新并不总是最好的。事实上,运行两个简单的查询可能更有效。因此,务必对这两种解决方案进行基准测试。

MySQL支持对 UPDATE JOIN 作为更新的一部分,而不是使用子查询。

然后你可以使用 IF() 功能(或 CASE )改变现状 prize

因此,如果您必须使用单个查询,请尝试以下操作:

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

或者这个替代方案:

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;

Andre Miller 11 年前

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 .

@幸运的 和前冠军,发布以下信息:

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
       )

Chris Cameron-Mills 15 年前

如果您只需要一条语句,您可以使用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 
              )

Travis 15 年前

您完全可以创建一个存储过程,将新获奖者的id作为参数。

然后可以在一行中调用该存储过程。

我在这里使用的是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

VVS 15 年前

由于您正在执行两个不同的更新,因此无需在一个查询中执行。如果因为两个查询中的一个可能会失败而担心数据不一致,那么可以使用事务并回滚两个查询中的任何一个。

您当前的查询是完全可读和可理解的,而发布的解决方案远非易读。