mysql - 如何分配变量来选择和更新字段

Question

这个问题已经有答案了:

MySQL Error 1093 - Can't specify target table for update in FROM clause (16 个回答)

已关闭 6 年前。

我的表中有两列，我正在尝试使用第一列的结果更新第二列，如下所示。

SET @pg = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'x' LIMIT 1)

现在上面的查询工作 (SELECT ...) 因为它从查询中返回一个 ID，所以我尝试使用该 ID 来更新另一个列，如下所示。

UPDATE wp_posts SET post_type = 'foo' WHERE ID = @pg; 

但由于某种原因，上述方法不起作用。

并且我收到以下错误。

1 queries executed, 0 success, 1 errors, 0 warnings

Query: set @pg = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'x' LIMIT 1) SELECT * ...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM wp_posts WHERE id = @pg' at line 3

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec

编辑/更新:

我也在尝试执行这个语句，它更简单并且做同样的事情

UPDATE wp_posts SET post_type = 'x' WHERE ID = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'z' LIMIT 1)

但由于某种原因，它不起作用。

Answer 1

您可以尝试使用 JOIN 进行更新

UPDATE wp_posts AS a 
       INNER JOIN wp_posts AS b ON a.id = b.id 
SET    a.post_type = 'x' 
WHERE  b.post_name = 'y' 
       AND b.post_status = 'z'   

或者将更新条件包装在另一个选择中

UPDATE wp_posts 
SET    post_type = 'x' 
WHERE  id IN (SELECT id
              FROM   (SELECT id 
                      FROM   wp_posts 
                      WHERE  post_name = 'y' 
                             AND post_status = 'z') AS SOURCE)