2

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.

Modern
  • 75
  • 2
  • 9

2 Answers2

5

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.

Deepak
  • 430
  • 1
  • 7
  • 14
  • Thanks for answering. It does work now! However, I still have questions about your code. Do I have to use * in the first inner query? Does the alias name tblTmp make the SQL work because it "thinks" that I don't use the SELECT from the products table? – Modern Jan 29 '22 at 08:33
  • In addition, don't I need to write a table name after the keyword FROM? In what you suggested there is not a table after the FROM clause, but a row. – Modern Jan 29 '22 at 08:45
  • 1
    @Modern check for more details https://dev.mysql.com/doc/refman/8.0/en/update.html . You can't use same table , the alias treat it like different table – Ergest Basha Jan 29 '22 at 08:46
  • @ErgestBasha Thanks for replying. I understood that I can't use the same table, however, what I do not understand is how SQL allows giving a row of table an alias of a table and considers it as a table? In this case `(SELECT id FROM products WHERE purchase_date LIKE '2019%' ORDER BY purchase_date DESC LIMIT 1)` returns one row, but an alias of a table is given. – Modern Jan 29 '22 at 08:48
  • @Modern a query always returns a table it doesn't matter if it is one row, it is treated like a table. A [function](https://dev.mysql.com/doc/refman/8.0/en/create-function-loadable.html) returns a value – Ergest Basha Jan 29 '22 at 08:53
  • @ErgestBasha I hadn't known that before. Thank you. – Modern Jan 29 '22 at 08:57
2

MySql is akward about update or delete statements that uses the target table also in a sub-query.

But you don't need the sub-query to use ORDER BY with a LIMIT

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

db<>fiddle here

Or the sargable way

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

If you want to use a query anyway? Then fool MySql. By selecting from a query or CTE.

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

DELETE FROM products t
WHERE id IN (SELECT id FROM (
  SELECT id 
  FROM products 
  WHERE YEAR(purchase_date) = 2019
  ORDER BY purchase_date DESC
  LIMIT 1
)d);
LukStorms
  • 28,916
  • 5
  • 31
  • 45