Error Code: 1093. You can't specify target table for update in FROM clause-CodePudding

Assume that I have a table of products and that there are only 2 fields; id and purchase_date. I want to delete the last product that was bought in 2019. I have tried to do it using the following query:

DELETE FROM products
WHERE id = (SELECT id
            FROM products
            WHERE purchase_date LIKE '2019%'
            ORDER BY purchase_date DESC
            LIMIT 1);

Unfortunately, the error that is written in the title appears. I know that this error is a known error and I have tried to look for solutions. I have looked over here and here, however, I couldn't understand how to change my query so it will work correctly.

I will be glad for help and explanations. Thanks in advance.

CodePudding user response：

Try this

DELETE FROM products
WHERE id = (SELECT * FROM
            (SELECT id 
            FROM products
            WHERE purchase_date LIKE '2019%'
            ORDER BY purchase_date DESC
            LIMIT 1)tblTmp);

The query is basically the same, except the inner select is wrapped inside another select. The most important thing to note is that the original select has been given an alias “tblTmp“. (The name tblTmp is arbitrary, you can give it any alias name.) The alias is important because assigning one will tell MySQL to create a temporary table from this select query. The temporary table may then be used as the source criteria for the update statement. The reason it is wrapped inside another query is because MySQL syntax does not let you assign an alias to a select query when it is part of an update statement. So we have to put it inside another query which, I suppose separates it from the update statement.

CodePudding user response：

The LIKE syntax in SQL is used for string values in CHAR/VARCHAR/TEXT datatypes.
But a DATE isn't exactly stored as text.
Date literals like '2020-02-02' are implicitly casted as dates.

To get the year from a date, you can simply use YEAR

DELETE FROM products
WHERE id IN (SELECT id
            FROM products
            WHERE YEAR(purchase_date) = 2019
            ORDER BY purchase_date DESC
            LIMIT 1);

Or the sargable way

DELETE FROM products
WHERE id IN (SELECT id
            FROM products
            WHERE purchase_date >= '2019-01-01'
              AND purchase_date  < '2020-01-01'
            ORDER BY purchase_date DESC
            LIMIT 1);