Query MySql delete from

Question

Query MySql delete from

Navigation

#1 by (5 votes)
#2 by (4 votes)

4

In the database I have.

Coches(matricula,año,modelo)
Es_Dueno(id_conductor,matricula)
Persona(id_conductor, nombre, direccion)

and I want to delete the model car Mazda of the client Santos

I made the following query:

DELETE FROM coches
    WHERE coches.matricula = (
        SELECT coches.matricula 
        FROM persona as A
        INNER JOIN es_dueno as B ON  A.id_conductor = B.id_conductor
        INNER JOIN coches ON B.matricula = coches.matricula 
        WHERE A.nombre ='Santos' AND coches.modelo = 'Mazda')

And I get the following error:

1093 - You can not specify target table 'Ej1_cars' for update in FROM clause

mysql sql

asked by Tin Rosso 24.11.2016 в 16:40

source

2 answers

4

The MySQL guide says that

You can not delete from a table and select from the same table in a subquery.

You can not delete from a table and use the same table within the subquery.

link

So, one option would be to obtain the registration first and then delete it in another query.

answered by 24.11.2016 в 16:46

How to format Chilean Rut with points and script when entering it in the input How does a return a || b of a function in recursion in JavaScript?

score 5 · Accepted Answer

Based on the response of @Pablo Claus, what you can do is to get the condition coches.modelo = 'Mazda' out of the subquery:

DELETE FROM coches
    WHERE coches.matricula IN (
        SELECT B.matricula 
        FROM persona as A
        INNER JOIN es_dueno as B ON  A.id_conductor = B.id_conductor
        WHERE A.nombre ='Santos')
    AND coches.modelo = 'Mazda'