entre Desarrolladores

Recibe ayuda de expertos

Registrate y pregunta

Es gratis y fácil

Recibe respuestas

Respuestas, votos y comentarios

Vota y selecciona respuestas

Recibe puntos, vota y da la solución

Pregunta

2votos

MySql DELETE usando SUBQUERY con misma tabla en FROM

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.

0voto

Leonardo-Tadei comentado

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?

0voto

Javi2EE comentado

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.

0voto

Leonardo-Tadei comentado

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...

0voto

Javi2EE comentado

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

4 Respuestas

2votos

Leonardo-Tadei Puntos227320

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!

0voto

Javi2EE comentado

Leonardo ando liado y aún no he podido probarlo, tiene muy buena pinta y si funciona es mejor solución que la otra, en cuanto lo ponga a prueba te comento.
Un saludo

1voto

Javi2EE comentado

Leonardo por fin he podido sacar algo de tiempo, la solución funciona y es obvio que es mejor que la que había marcada como solución, agradezco tu ayuda crack.

1voto

Leonardo-Tadei Puntos227320

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

0voto

Javi2EE comentado

Hola @Leonardo-Tadei, esa consulta elimina todos los precios de una tarifa menos el de mayor id, yo quiero eliminar cuando en una misma tarifa tenga dos precios para el mismo producto, dejando solo el ultimo para cada producto. He editado la pregunta ya que no se entendía del todo bien.
Por otro lado cuando se utiliza un alias en el from si hay que especificar el DELETE p.

1voto

Leonardo-Tadei comentado

Hola @Javi2EE,

en tu query, al pasar el parámetro :tarifa para el campo p.tarifa_id, significa que el resultado solo devolverá los precios que tengan ese tarifa_id y no otros, por lo tanto, el borrar todos menos el de mayor ID, borrará todos los precios menos el último de la tarifa por la que estás filtrando los valores.

El poner el el WHERE un ID de tarifa específico, cambia mucho el funcionamiento de la query, y vos lo estás poniendo.

Cuál fue tu idea al poner WHERE p.tarifa_id = :tarifa ???

Tal vez no esté entendiendo bien tu pregunta o el parámetro :tarifa no tenga sentido :-(

Saludos cordiales.

PD: según el manual de MySQL y en concordancia con el error que obtenés, no se pueden usar alias en los DELETE, lo que significa que no debería ir nada entre el DELETE y el FROM.

0voto

Javi2EE comentado

Hola @Leonardo-Tadei, no se esta entendiendo bien la pregunta, la he ampliado un poco mas con un ejemplo para que veas a que me refiero.

Respecto a lo otro al menos en Mysql 5.7 se puede utilizar un alias en el FROM, lo he probado y funciona, y en esta consulta es necesario utilizarlo ya que tengo que hacer referencia a la query principal desde la subquery. utilizar alias en delete

1voto

bl4z3r Puntos16850

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.

0voto

Javi2EE comentado

@bl4z3r Gracias por la ayuda pero MySql sigue tirando el error > You can't specify target table 'p' for update in FROM clause
He puesto una solución que me han dado utilizando una tabla temporal.

1voto

Javi2EE Puntos6630

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

0voto

Peter comentado

Gracias por compartir la solución!

Por favor, accede o regístrate para responder a esta pregunta.

Otras Preguntas y Respuestas


...

Bienvenido a entre Desarrolladores, donde puedes realizar preguntas y recibir respuestas de otros miembros de la comunidad.

Conecta