题 使用WHERE SELECT子查询错误的MYSQL更新


我有一个问题,让选择子查询工作 UPDATE。我正在尝试以下内容:

UPDATE foo
   SET bar=bar-1
 WHERE baz=
      (
       SELECT baz
       FROM foo
       WHERE fooID='1'
      )

哪里 foo 是具有主键的表名 fooIDbar 和 baz 属于INT类型。执行此操作时,我收到以下错误:

Error: A query failed. You can't specify target table 'foo' for update 
in FROM clause

24
2017-08-04 15:26


起源


可能重复 SQL删除:无法在FROM子句中指定要更新的目标表 - ajreal
stackoverflow.com/search?q=specify+target+table - ajreal


答案:


从这里: 网络文章 “出现此错误的原因是,当您在内部选择中使用相同的表作为更新条件时,MySQL不允许更新表。”本文接着提供了一个解决方案,即使用临时表。

使用此示例,您的更新应为:

update foo
set bar = bar - 1
where baz in
(
  select baz from
  (
    select baz
    from foo
    where fooID = '1'
  ) as arbitraryTableName
)

49
2017-08-04 15:35



这样做了! :) 非常感谢!! - Erik
但我必须说,我很惊讶这不是更干净的支持...... - Erik
一旦我有机会,我会重新考虑这个,但只是一个未经考验的想法,我想我会扔掉那里:不会更清楚地解决OP的错误就是不使用 任何 在UPDATE查询中子选择并改为JOIN foo?嵌套的SELECT语句在这里工作,它们是我一直用于这种情况的解决方案,因为看到你的答案,但它们非常hacky - 如果JOIN工作,感觉就像'正确'的解决方案。 - Mark Amery
我同意嵌套选择可能不是最好的解决方案。我没有考虑OP提出的解决方案。加入似乎很好,但是对于SQL,我常常不愿意说一个功能正常的解决方案是'正确'而另一个是'不对'。只是不同,也许,不太理想。 - DwB
我尝试了另一种方式,它也有效,更新foo f,(从foo中选择baz,其中fooID ='1')一组f.bar = f.bar - 1其中f.baz = a.baz。我的mysql版本是5.6.27-0ubuntu0.14.04.1-log - zhuguowei


由于错误1093错误1093(ER_UPDATE_TABLE_USED)SQLSTATE = HY000。 解决方法是创建一个临时表。

CREATE TEMPORARY table foo_bak (SELECT baz from foo WHERE fooID='1');

UPDATE foo
  SET foo.bar=foo.bar-1
WHERE foo.baz =
  (
    SELECT baz
    FROM foo_bak
  );

DROP TABLE foo_bak;

4
2017-08-04 15:29



是的,我尝试命名一切,但没有运气..似乎你不能在子查询中使用相同的表...我觉得有点傻。 - Erik
是的,如果我没记错的话,PostgreSQL允许这样做。 Dwb的解决方案更清洁。 - ace
它适用于我使用mysql 5.6!很棒的答案! - Tarsis Azevedo
如果您创建一个临时表,则不需要删除它,因为在脚本结束时会自动删除临时表 - Thomas Williams


据我所知,在更新表时,Mysql将其锁定以进行安全更新。您无法选择数据并更新您尝试的同一个表。

那些文本可以帮助你


0
2017-08-04 15:30



但是,子查询不应该是不相关的吗? (即内部查询首先运行,外部查询运行之后)...这是一个非常有用的查询,能够运行,它似乎很奇怪,它是不允许的。 :( - Erik


在某些情况下,您还可以利用MySQL变量。例如。:

SET @id1 = (SELECT id FROM foo WHERE name = 'parent');
UPDATE foo SET parent_id = @id1 WHERE name = 'emails';

0
2018-03-29 12:23