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!

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.