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

SQL:具有动态列值分配的UPDATE语句

sql-update sql mysql

Joel · 技术社区 · 15 年前

想象一下下面的SQL查询:

UPDATE MYTABLE
SET COL2 = (SELECT COL2 + 1 FROM (SELECT MAX(COL2) FROM MYTABLE) AS X)
WHERE ID IN (1,2,3,4,5)

假设在执行更新之前,max(col2)为1。

我的意图是,对于id=1的更新,col2更新为“max(col2)+1”(即2),而对于后续更新,则重新评估“max(col2)+1”,以便id=2、col2=3和id=3、col2=4等……

实际上,对于所有行(id=1,2,3,4,5),col2的值都是2。

在每次更新时,是否有一个聪明的方法让max(col2)+1“重新评估”值?我知道这样做可能会有性能问题,但我还是很好奇!是否有更好的选择(不涉及多个更新语句)?

btw:如果您对上面的查询(嵌套的内部表)所使用的语法感到疑惑,请参见以下内容: SQL : Using the target table in an UPDATE statement in a nested FROM clause

5 回复 | 直到 12 年前

Quassnoi 15 年前

UPDATE mytable, (
  SELECT @loop := MAX(col1)
  FROM
    mytable
  ) o
SET col1 = (@loop := @loop + 1)

你在这里遇到的叫做 query stability .

任何查询都看不到自己所做的更改,也看不到以下查询:

UPDATE mytable
SET col1 = col2 + 1
WHERE col1 > col2

永远不会结束。

Bill Karwin 15 年前

我可以这样做:

SELECT MAX(col2) FROM mytable INTO @max;

UPDATE mytable
SET col2 = @max:=@max+1
WHERE id IN (1,2,3,4,5)
ORDER BY id;

我在MySQL5.1.30上测试过这个,它工作正常。

我设定 @max 首先是变量,因为我发现@quassnoi在笛卡尔积中做这件事的诀窍是不必要的,而且可读性较差。

注意mysql支持 ORDER BY 在一个 UPDATE 语句(这不是标准的SQL),您应该这样做,以确保按照您想要的顺序更新值。否则,MySQL不保证特定的顺序。

Learning 15 年前

declare @loop int
select @loop = 1

UPDATE MYTABLE
SET COL1 = @loop,
    @loop = @loop+1
WHERE ID IN (1,2,3,4,5)

Kristen 15 年前

是否有一个聪明的方法在每次插入时使max(col1)+1“重新评估”的值?

除非逐个更新行:(

这可以在mssql中使用,不了解mysql,但可能会给您一个想法:

DECLARE @Counter int

SELECT @Counter = MAX(COL2) FROM MYTABLE

UPDATE MYTABLE
SET @Counter = @Counter + 1,
    COL1 = @Counter
WHERE ID IN (1,2,3,4,5)

编辑:我认为set语句可以在mssql中简化为:

SET @Counter = COL1 = @Counter + 1

-1

lc. 15 年前

~~它应该在每次插入时重新评估它;可能col2的最大值没有变化。您确定不想选择max(col 一 )?~~ 再次阅读您的问题,您混淆了术语插入和更新(现在已修复),我被抛出一个循环。

您试图一次更新许多记录,但是让它像是单独更新每个记录一样工作。这不符合你的意愿。

您可以创建一个存储过程来遍历(1、2、3、4、5)中ID所在的每个记录,并分别进行更新。或者,既然您只做了5行,为什么不将语句复制粘贴五次,并将WHERE子句更改为 WHERE ID = 1 (在后面的行中是2、3、4、5)。