MySql DELETE using SUBQUERY with same table in FROM

Question

MySql DELETE using SUBQUERY with same table in FROM

Navigation

#1 by (6 votes)

4

Database manager system: MySql 5.7.11

I'm trying to create a SQL query to delete all the records that meet the following condition:

This is a tariff system, a tariff table is related to the price table in a one-to-many relationship, and each price is related to a product.

Because tariffs are created through batch actions, sometimes in a tariff, several prices are recorded for the same product. My intention is to keep in each tariff only the last price (the highest id) for each product and eliminate the previous prices, for this I am using the following query:

DELETE p 
    FROM  ges_precio as p 
    WHERE p.tarifa_id = :tarifa 
          and id < 
              (select max(a.id) as id 
               from ges_precio  a 
               where a.tarifa_id = :tarifa 
                     and p.producto_id = a.producto_id 
               )

But I'm getting the following error:

You can not specify target table 'p' for update in FROM clause

I have tried to encapsulate the subquery in another select and use an alias to solve it as explained here but it does not work either.

I'm looking for a way to do the query, without using a subquery in the FROM that consults the price table.

UPDATE:

I will give an example so that I understand better what I intend to do, this is the table ges_precio:

id | amount | rate_id | product_id

1 | 24 | 1 | 24

2 | 16 | 1 | 53

3 | 18 | 1 | 24

Right now tariff 1 consists of three prices, the problem is that two of them refer to the same product 24, in this case I want to discard the oldest price for this rate and product 24, that is to say after using the query the table should look like this:

id | amount | rate_id | product_id

2 | 16 | 1 | 53

3 | 18 | 1 | 24

mysql sql

asked by Javi2EE 10.07.2016 в 18:25

source

1 answer

phonegap, android, iOS and web browsers Help in solving algorithm showing repeating figures - Java

score 6 · Accepted Answer

I have had this type of problem several times (keep the last record of a relationship). The way I've solved it has almost always been with temporary tables and nested queries.

CREATE TEMPORARY TABLE temp
  SELECT id FROM ges_precio 
  WHERE tarifa_id = ?? AND product_id = ??; -- [1]

DELETE FROM ges_precio 
  WHERE id < (SELECT MAX(id) FROM temp) 
  AND product_id = ??; -- [2]

DROP TEMPORARY TABLE temp; -- [3]

Like this:

You create a temporary table with all the prices for a specific product and rate.

You remove all items that are less than the last price ( MAX(id) )

You delete the temporary table.

Update:

Due to the issue of efficiency proposed by Javi2EE, I write some proposals for improvements that come to mind:

First, temporary tables are tables that exist in volatile memory during the execution of the query, have a maximum size and are efficient in use, since they do not use a disk (read temporary table section )

In the event that you run a few times a day (in a cleaning cron, for example). I can think of the following:

CREATE TEMPORARY TABLE temp
  SELECT MAX(id) as id, tarifa_id, producto_id 
  FROM ges_precio 
  GROUP BY tarifa_id, producto_id; -- Crear una tabla con solo los id de precio más altos, así se baja la cantidad total de filas de la tabla temporal.

DELETE g FROM ges_precio g
  WHERE id < (SELECT id FROM temp t WHERE t.tarifa_id = g.tarifa_id AND t.producto_id = g.producto_id ); -- y se borra usando un INNER JOIN con la temporal

DROP TEMPORARY TABLE temp; -- [3]

Another option is to use variables in the connection instead of temporary tables, so you could run this query several times, without the worry of using many temporary tables. Example:

SET @id_temp = NULL;

SELECT MAX(a.id) INTO @id_temp 
  FROM ges_precio
  WHERE tarifa_id = :tarifa 
    AND producto_id = :producto_id;

DELETE FROM ges_precio 
  WHERE id < @id_temp;

In this way you only create a variable in the connection session with the necessary value and use it later to delete.

I hope I have helped with this last.