[MDEV-6272] SQL Error on perfectly valid query Created: 2014-05-26  Updated: 2014-05-26  Resolved: 2014-05-26

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Timwi Terby Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Windows 7


 Description   

I ran the following SQL query:

update image set image.isMain=1 where not exists (
select * from image i2 where image.article_id=i2.article_id and image.id < i2.id)

I receive the following error message:

SQL Error (1093): Table 'image' is specified twice, both as a target for 'UPDATE' and as a separate source for data

It is perfectly valid (and often necessary, as in this case) to specify a table twice in this way. This query should work and not return an error.

 Comments   
Comment by Elena Stepanova [ 2014-05-26 ]

Hi,

It is an old documented MySQL limitation that has survived many versions, see for example MySQL manual for the latest MySQL GA 5.6:
http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html:

In general, you cannot modify a table and select from the same table in a subquery.
...

Generated at Thu Feb 08 07:10:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.