33

I have an issue with getting select sub-queries to work on an UPDATE. I'm trying something like the following:

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

Where foo is the table name with primary key fooID. bar and baz are of type INT. When executing this I get the following error:

Error: A query failed. You can't specify target table 'foo' for update 
in FROM clause
halfer
  • 19,824
  • 17
  • 99
  • 186
Erik
  • 2,276
  • 1
  • 20
  • 20
  • possible duplicate of [SQL Delete: can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause) – ajreal Aug 04 '11 at 15:32
  • http://stackoverflow.com/search?q=specify+target+table – ajreal Aug 04 '11 at 15:32

4 Answers4

63

From this web article

The reason for this error is that MySQL doesn’t allow updates to a table when you are also using that same table in an inner select as your update criteria. The article goes on to provide a solution, which is to use a temporary table.

Using this example, your update should be this:

update foo
set bar = bar - 1
where baz in
(
  select baz from
  (
    select baz
    from foo
    where fooID = '1'
  ) as arbitraryTableName
)
DwB
  • 37,124
  • 11
  • 56
  • 82
  • 1
    I'm going to revisit this as soon as I get the chance, but just an untested thought I figured I'd throw out there: wouldn't a cleaner way to work around the OP's error be to not use *any* subselects and instead JOIN foo on itself in the UPDATE query? The nested SELECT statements here work, and they're the solution I've always used for this situation since seeing your answer, but they are quite hacky - if the JOIN works, it feels like the 'right' solution. – Mark Amery Mar 19 '14 at 15:00
  • I agree that nested select may not be the best solution. I didn't consider solutions other than the one proposed by the OP. Join does seem good, but with SQL, I'm often not willing to say one functioning solution is 'right' and another is 'not right'. Just different and, perhaps, less optimal. – DwB Mar 19 '14 at 16:33
  • I tried another way and it also works, update foo f, (select baz from foo where fooID = '1') a set f.bar = f.bar - 1 where f.baz = a.baz . My mysql version is 5.6.27-0ubuntu0.14.04.1-log – zhuguowei Nov 11 '15 at 09:13
  • @DWB At least MariaDB 10.3 lifted that limitation [more](https://stackoverflow.com/a/50749204/5070879) – Lukasz Szozda Dec 26 '18 at 19:09
5

Because of error 1093 Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000. The work around is to create a temporary table.

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;
ace
  • 7,293
  • 3
  • 23
  • 28
  • yeah i tried naming everything but no luck.. seems like you can't use the same table in the subquery... which i find a bit silly. – Erik Aug 04 '11 at 15:37
  • Yes, if I remember correctly, PostgreSQL allows this. Dwb's solution is much cleaner. – ace Aug 04 '11 at 15:40
  • If you create a tempory table you don't need to drop it, as a tempory table gets auto deleted when the script ends – Thomas Williams Aug 16 '17 at 22:08
2

In some cases you can also take advantage of the MySQL variable. e.g.:

SET @id1 = (SELECT id FROM foo WHERE name = 'parent');
UPDATE foo SET parent_id = @id1 WHERE name = 'emails';
Lukas Jelinek
  • 2,337
  • 1
  • 19
  • 12
1

So far as I know, when updating a table, Mysql locks it in order to do a safe update. You cannot select data and update the same table as you're trying.

Those texts should help you

P. R. Ribeiro
  • 2,999
  • 1
  • 19
  • 20
  • 1
    But aren't sub queries supposed to be uncorrelated? (i.e. inner query runs first, outer query runs after)... This is a very useful query to be able to run, it seems weird that it would not be allowed. :( – Erik Aug 04 '11 at 15:35