如何在MYSQL UPDATE中使用表别名

Question

我需要使用PHP的mysql_query()函数更新一个MySQL表格。因此，我需要在一条语句中发送以下UPDATE查询：

SET @i = 0;

UPDATE mytable SET id=(@i:=@i+1);

我读过一些示例，可以使用SELECT语句和表别名来实现这一点，例如：

SELECT @rn:=@rn+1 AS rank, t1.* FROM (SELECT * FROM mytable) t1, (SELECT @rn:=0) t2

有没有一种方法可以在需要使用的UPDATE语句中使用这些表别名？

编辑：

根据Topher Hunt的回答，我认为我可以使用以下语句创建mytable的副本：

CREATE TABLE mytable_copy SELECT @rn:=@rn+1 AS id, t1.* FROM (SELECT * FROM mytable) t1, (SELECT @rn:=0) t2

然后删除mytable并将mytable_copy重命名为mytable。这个语句会创建一个与mytable完全相同的副本，其中的字段类型和长度与mytable中的字段类型和长度相同吗？

- Ignacio Sáyago

Answer 1

如果你在表格之间使用JOIN，那么SELECT语句和UPDATE语句的语法非常相似。例如：

SELECT * 
FROM table1 a
    JOIN table2 b ON a.something = b.something ## (conditions for linking tables)
    JOIN table3 c ON b.something = c.something
WHERE a.something = 'value'

一个连接三个表的SELECT语句，只需删除SELECT行，将FROM改为UPDATE，并添加一个“SET”子句来说明您想要更改的内容，就可以变成一个UPDATE语句。像这样：

UPDATE table1 a
    JOIN table2 b ON a.something = b.something ## (conditions for linking tables)
    JOIN table3 c ON b.something = c.something
SET a.variable = 'value2', 
    b.something = 'value3',
    c.somethingelse = 'value4'
WHERE a.something = 'value';

如果需要，您可以同时更改许多字段；只需用逗号分隔每个 SET 项目即可。

- Topher Hunt

---

非常感谢。在这种特殊情况下，mytable没有索引。因此很难使用JOIN和WHERE子句。这就是为什么我想使用UPDATE语句在mytable中拥有唯一索引的原因。 - Ignacio Sáyago

好的，谢谢你澄清。我对你的表如何设置以使得JOINS无法完成任务感到困惑，我怀疑为了执行更新操作而删除和重新创建表是不必要的。为了帮助我理解，你能否澄清一下新值（你在更新表中保存的值）来自何处？你是从另一个表获取该值，还是根据PHP变量插入为一个“常量”元素？ - Topher Hunt

更新后的值将只是连续的数字：1、2、3、4...我试图使用PHP变量（i=i+1）来更新表中的每一行。然而，由于该表没有索引，可能会有多行满足“WHERE a=somevalue AND b=someothervalue”的条件。该表有10个字段。在所有10个字段中没有相同值的行，因此我可以使用WHERE a=somevalue AND b=someothervalue AND c=yetanothervalue ...AND j=finalvalue，但我正在尝试更简单的方法来完成它。 - Ignacio Sáyago

啊，所以你想在事后添加类似唯一顺序ID值的东西？如果你通过注入PHP代码来实现这个目标，你将被限制每行在数据库中只能运行一个UPDATE查询，因为你注入的任何PHP都将应用于所有更新的行。然而，你可以使用Mysql变量来使用这种技术一次更新多行（速度非常快，但是请先测试以确保你对这种技术感到舒适！）https://dev59.com/H2ox5IYBdhLWcg3w9o-i - Topher Hunt

Answer 2

为什么不直接删除这一列并添加一个具有自动增量的新主键列呢？这样将自动分配您需要的值。

ALTER TABLE mytable DROP COLUMN id;

ALTER TABLE mytable ADD ( id int AUTO_INCREMENT PRIMARY KEY );

-or- as you requested...

UPDATE mytable
  JOIN (SELECT @xxx := 0) AS v1 
   SET id = (@xxx := @xxx + 1)
;

- Jon Armstrong - Xgc

---

非常感谢！你是对的，删除该列并添加一个新的自增主键是最直接的方法。 - Ignacio Sáyago