Bug #23353 cannot update a table and select from the same table in a subquery
Submitted: 17 Oct 2006 1:49 Modified: 17 Oct 2006 7:09
Reporter: Fauzan Badriawan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:Future OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: ALL

[17 Oct 2006 1:49] Fauzan Badriawan 
Description:
It is great to enable updating table with the same table in subquery, like MSSQL, FireBird and SQLite, as new feature in the future versions. 

How to repeat:
Is there any plan of this matter in the future?
[17 Oct 2006 7:09] Valeriy Kravchuk 
Thank you for a reasonable feature request.
[4 Aug 2009 17:46] Valeriy Kravchuk 
Bug #46534 was marked as a duplicate of this one.
[1 May 2022 3:24] Mohit jain 
If you can't do

UPDATE table SET a=value WHERE x IN
    (SELECT x FROM table WHERE condition);
because it is the same table, you can trick and do :

UPDATE table SET a=value WHERE x IN
    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)
[17 Nov 2022 2:20] Josh McCullough 
Yes, but we "shouldn't have to" do that trick. This one gets me every time!