mysql - MySql Error code: 1093- You can't specify target table 'n1' for update in FROM clause

Question

I have two tables: T1 and T2

T1
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+
   |ID      | NAME    | LASTNAME     |REF       |DNI          | COUNTRY   | MONTH  | LAST_DATE   | 
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+
   |  1     |Juan     |Valverde      |23054     |6124123      | EEUU      |  APRIL |  2021-09-03 | 
   |  2     |Mark     |Márquez       |42451     |9751434      | MEXICO    |  MAY   |  2020-05-05 | 
   |  3     |Mary     |Jeans         |19784     |8624244      | SPAIN     |  JULY  |  2019-15-18 |
   |  4     |Phillip  |Butters       |25054     |1251244      | FRANCE    |  OCTOB |  2018-01-01 | 
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+

 T2
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+
   |ID      | NAME    | LASTNAME     |REF       |DNI          | COUNTRY   | MONTH  | LAST_DATE   | 
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+
   |  1     |Juan     |Valverde      |23054     |6124123      | EEUU      |  APRIL |  2019-09-03 | 
   |  2     |Juan     |Valverde      |23054     |6124123      | PANAMA    |  JAN   |  2021-10-10 | 
   |  3     |Mark     |Márquez       |42451     |9751434      | MEXICO    |  MAY   |  2020-05-05 | 
   |  4     |Mark     |Márquez       |42451     |9751434      | GERMANY   |  JUN   |  2021-09-03 | 
   |  5     |Mary     |Jeans         |19784     |8624244      | SPAIN     |  JULY  |  2016-15-18 |
   |  6     |Mary     |Jeans         |19784     |8624244      | PORTUGAL  |  JULY  |  2017-10-10 | 
   |  7     |Mary     |Jeans         |19784     |8624244      | PERU      |  FEB   |  2015-05-30 |
   |  8     |Phillip  |Butters       |25054     |1251244      | FRANCE    |  OCTOB |  2018-01-01 | 
   |  9     |Phillip  |Butters       |25054     |1251244      | BRAZIL    |  DEC   |  2018-04-01 | 
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+

I want to compare "ref", "dni" and "last_date(T2) > last_date(T1)". if the date of T2 > date of T1... we keep the whole line in table T2, otherwise we delete it. The table would look like this:

       T2
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+
   |ID      | NAME    | LASTNAME     |REF       |DNI          | COUNTRY   | MONTH  | LAST_DATE   | 
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+
   |  2     |Juan     |Valverde      |23054     |6124123      | PANAMA    |  JAN   |  2021-10-10 |  
   |  4     |Mark     |Márquez       |42451     |9751434      | GERMANY   |  JUN   |  2021-09-03 | 
   |  9     |Phillip  |Butters       |25054     |1251244      | BRAZIL    |  DEC   |  2018-04-01 | 
   +--------+---------+--------------+----------+-------------+-----------+--------+-------------+

I have been trying this query:

USE CC_TEST;
DELETE n1 FROM TEST.T2 n1 INNER JOIN TEST.T1 n2 WHERE n1.REF=n2.REF AND n1.DNI=n2.DNI AND
(n1.LAST_DATE = (SELECT MAX(LAST_DATE) FROM TEST.T2)) > (n2.LAST_DATE = (SELECT MAX(LAST_DATE) FROM TEST.T1));

and I get the following error:

Error code: 1093- You can't specify target table 'n1' for update in FROM clause

Can you help me, please??

标签： mysql

Answer 1

Your logic is a bit hard to follow, but I think you want:

DELETE n1
    FROM TEST.T2 n1 INNER JOIN
         (SELECT n2.ref, n2.dni, MAX(LAST_DATE) as last_date
          FROM TEST.T1 n2
          GROUP BY n2.ref, n2.dni
         ) n2
         ON n1.REF = n2.REF AND n1.DNI = n2.DNI AND
            n1.LAST_DATE > n2.LAST_DATE;