MySQL Bugs: #6980: Error 1093 - You can't specify target table for update in FROM

Bug #6980	Error 1093 - You can't specify target table for update in FROM
Submitted:	3 Dec 2004 10:20	Modified:	6 Feb 2014 14:22
Reporter:	Sanvido Federico	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	4.1.7	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
A particular Update with Inner Join and an 'exists' in the where clause with an inner join with the same table.
a) mysql 4.1.3 beta version behaves correctly; 
b) mysql 4.1.7 gamma returns the following error :
"SQLError# 1093 - You can't specify target table 't_sipa_stato' for update in FROM clause"

How to repeat:
----------------------------------
-- Create Table
----------------------------------
CREATE TABLE t_sipa_stato (
  COD_SIPA       varchar(12) NOT NULL default '',
  COD_LATO       char(1)     NOT NULL default '',
  COD_BOTTIGLIA  varchar(12)          default NULL,
  FLG_ANOMALIA   tinyint(1)  NOT NULL default '0',
  PRIMARY KEY  (COD_SIPA,COD_LATO),
  KEY IDX_LATI_BOTTIGLIA (COD_BOTTIGLIA),
) ENGINE=InnoDB;

CREATE TABLE t_bottiglie (
  COD_BOTTIGLIA  varchar(12) NOT NULL default '',
  DES_BOTTIGLIA  varchar(50) NOT NULL default '',
  COD_COLORE     varchar(5)           default NULL,
  PRIMARY KEY  (COD_BOTTIGLIA),
) ENGINE=InnoDB;

----------------------------------
-- Update Test
----------------------------------

Update T_BOTTIGLIE Inner join T_SIPA_STATO On T_BOTTIGLIE.COD_BOTTIGLIA = T_SIPA_STATO.COD_BOTTIGLIA
Set T_SIPA_STATO.FLG_ANOMALIA = 1
Where Exists
( Select *
    from T_BOTTIGLIE As BOTT Inner join T_SIPA_STATO As STAT On BOTT.COD_BOTTIGLIA = STAT.COD_BOTTIGLIA
   Where T_SIPA_STATO.COD_SIPA = STAT.COD_SIPA And T_SIPA_STATO.COD_LATO <> STAT.COD_LATO
   And T_BOTTIGLIE.COD_COLORE <> BOTT.COD_COLORE
)

Suggested fix:
Alternative SQL script to obtain the same result.

Hi,

Thank you for the report, but currently you can't update the same table which you use in the SELECT part.
This behaviour is documented at:
http://dev.mysql.com/doc/mysql/en/UPDATE.html

Subquery returns more than 1 row

Subquery returns more than 1 row

Ha! beat it, this bug should be closed...

UPDATE sales AS t1, sales AS t2 SET t1.idproduct = 1 WHERE t2.id > 1

Still there in 5.1.

update m
set flag = 1
where id in 
  (select max(grouped.id) from a as grouped group by year(grouped.timestamp))

No end-user reason why this shouldn't work. And it's a small excuse to have it documented (for years!)

Simple, just alias the table and it's "solved", like this

INSERT INTO groupBolaoLock (`groupId`, `groupCount`) VALUES (
(
SELECT IF(
     MAX(groupBolaoLockB.groupId) IS NULL,
     1, 
          IF(
               groupBolaoLockB.groupCount = 10,
               MAX(groupBolaoLockB.groupId) + 1, 
               MAX(groupBolaoLockB.groupId)
          )
     )
     FROM groupBolaoLock groupBolaoLockB
),1) ON DUPLICATE KEY UPDATE groupCount = groupCount + 1

Otávio, did you see that in last comment's example the table is aliased?

The bug looks really fixed, but it's not about table alias anyway.

I retried it on 5.1.41, and it worked fine.

Yet another reason to use Postgresql instead!  I have used MySQL for years and over time I have found the limitations of it.  Using Postgresql this is not an issue at all!  I created a table, ran an update with a select as a filter with no problems.  Also if Postgresql is rebooted, empty innodb tables do not reset their auto incremented values - caused a huge issue of overwriting data months ago!  The more I use the two the more I want to use just one - Postgresql!  Simply referring to the documentation when other databases behave properly is hardly an excuse!

In my reasoning I would think that the DBMS should run the select and then just apply the result to the update statement - that is how all subqueries work!

I have a nice workaround:

Instead of
UPDATE `projects`  
    SET `parent_id`=(
        SELECT p.`id` FROM `projects` AS p WHERE p.`identifier`='ic'
    )
WHERE `identifier`='p'

I write this
UPDATE `projects`  
    SET `parent_id`=(
        SELECT p.`id` FROM (SELECT * FROM `projects`) AS p WHERE p.`identifier`='ic'
    )
WHERE `identifier`='p'

Hopes this helps anybody.

this query gives the error #1093 - You can't specify target table 'user' for update in FROM clause query is : 

update user set iFriends =  (select count(*) as tot from friends join user as u on u.iUserId = IF(friends.iSenderId = 1,friends.iReceiverId,friends.iSenderId)  where (friends.iSenderId = 1 or friends.iReceiverId = 1) and friends.eStatus = 'Accept' and u.eStatus = 'Active') where user.iUserId = '1'