Question

Hola a todos:

Estoy tratando de crear una consulta SQL para una base de datos MySql 5.7.11 para eliminar todos los registros que cumplen con la siguiente condición:

Se trata de un sistema de tarifas, una tabla tarifa esta relacionada con la tabla precios en una relación uno-a-muchos, y cada precio se relaciona con un producto.

Debido a que las tarifas se crean mediante acciones en lote a veces en una tarifa quedan registrados varios precios para un mismo producto. Mi intención es mantener en cada tarifa solo el ultimo precio(el de mayor id) para cada producto y eliminar los precios anteriores, para ello estoy utilizando la siguiente consulta:

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 
               )

Pero estoy recibiendo el siguiente error:

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

He tratado de encapsular la subquery en otro select y usar un alias para solucionarlo pero no he podido.

Estoy buscando una forma de realizar la consulta, sin usar una subquery en el FROM que consulte a la tabla precio, pero no se me ocurre nada.

ACTUALIZACIÓN:

Voy a poner un ejemplo para que se entienda mejor lo que pretendo hacer, esta es la tabla ges_precio:

*La consulta se ejecutará para la tarifa 1 así que se pueden ignorar los registros de la tarifa 2

id | importe | tarifa_id | producto_id

1 | 24 | 1 | 24
2 | 16 | 1 | 53
3 | 18 | 1 | 24

4 | 20 | 2 | 23
5 | 22 | 2 | 24
6 | 18 | 2 | 23

Ahora mismo la tarifa 1 consta de tres precios, el problema es que dos de ellos hacen referencia al mismo producto 24, en este caso quiero desechar el precio mas antiguo para esta tarifa y el producto 24, es decir tras usar la consulta para la tarifa 1 la tabla debería quedar de la siguiente manera:

id | importe | tarifa_id | producto_id

2 | 16 | 1 | 53
3 | 18 | 1 | 24

4 | 20 | 2 | 23
5 | 22 | 2 | 24
6 | 18 | 2 | 23

Espero que me puedan ayudar.

Comments

Gracias por los datos de ejemplo.

Querés hacer el proceso todo de una vez en una única query o querés hacerlo según para un único tarifa_id pasado como parámetro?

Podrías ampliar la tabla de datos de ejemplo para el caso de que haya más de 1 tarifa_id que también tenga 2 precios?

---

Hola Leonardo gracias a ti por la ayuda, quiero hacerlo para un único tarifa_id, ya que este método se ejecuta después de guardar una tarifa, así que me basta con realizarlo para esa tarifa después de guardarla y se pueden ignorar el resto de tarifas. Igualmente he añadido una tarifa 2 por si ayuda.

---

Hola @Javi2EE,

si es para una única tarifa por vez, la query en mi respuesta acá abajo funciona. Cargá unos datos de prueba, probala y contanos...

---

Hola Leonardo lo he probado y no funciona, esa consulta borra todos los precios de una tarifa menos el de mayor id pero debe hacer esto para cada producto, no para toda la tarifa.
Un saludo

Answer 1

Hola @Javi2EE,

recién hoy tuve tiempo de ver esto. Tardé un rato en entender lo que querías...

No se pueden hacer DELETE con una SUBQUERY porque no está permitido en MySQL

Subqueries
You cannot delete from a table and select from the same table in a subquery.
Está en este enlace

Se puede solucionar de forma muy simple y eficiente haciendo el DELETE sobre tablas múltiples, usando ALIAS para la misma tabla.

Esta query resuelve tu problema:

DELETE g1 FROM ges_precio g1,ges_precio g2 
WHERE g1.tarifa_id =1 
AND g1.tarifa_id = g2.tarifa_id 
AND g1.producto_id = g2.producto_id 
AND g1.id < g2.id

y ciertamente consume menos recursos que crear primero una tabla temporal para hacer el filtrado.

Saludos cordiales!

Answer 2

Hola @Javi2EE,

Tal vez lo puedas solucionar más fácilmente sin usar una subquery, con algo de la forma:

DELETE FROM  ges_precio as p 
WHERE p.tarifa_id = :tarifa 
and p.id < MAX(p.id);

es decir, borrar los registros con tarifa_id = :tarifa y que tengan por ID un valor menor que el máximo ID existente.

Por otra parte, tu query fallla por un error de sintaxis: en un DELETE no va nada entre el DELETE y el FROM.

Saludos cordiales

Answer 3

Al parecer, lo que quieres hacer es eliminar registros duplicados sobre una ventana.

Entonces, lo que necesitas es hacer lo siguiente:

DELETE FROM ges_precio AS p
WHERE p.tarifa_id = :tarifa
AND producto_id IN 
    (SELECT producto_id
     FROM ges_precio
     WHERE tarifa_id = :tarifa
     GROUP BY producto_id
     HAVING COUNT(producto_id) > 1)
AND p.id < MAX(p.id)

Traducido sería: "Borrame todos los registros de la tabla ges_precio dónde tarifa_id sea igual al parámetro tarifa y los productos que estén dentro de todos los productos que tenga la tarifa pasada por parámetro y tengan más de un registro y el id sea mayor de todos".

Posiblemente falte trabajarla más, a la consulta pero creo que ese es el aproximamiento a resolver tu problema.

Answer 4

Tras darle muchas vueltas al tema finalmente una persona me ha dado la solución utilizando una tabla temporal, que solo mantiene el ultimo registro de una relación, la pongo aquí por si ayuda a alguien:

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.

DELETE 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; 

Muchas gracias a todos.
Un saludo