[SOLUCIONADO] Borrar un registro con JOIN

[Hyemin]

Hola como estan?

Tengo una base de datos donde hay 2 tablas, autos y vehiculos que estan referenciados por matricula.

tengo que hacer una consulta para eliminar un registro y le estoy dando vueltas hace rato pero no doy con la sintaxis correcta

Estoy haciendo

Código SQL:

DELETE    FROM vehiculo INNER JOIN autos 
ON (vehiculo.matricula=autos.matricula) 
WHERE autos.matricula="+matricula)
 

aclaroque +matricula es la variable concatenada que almacena el valor "matricula"

[gnzsoloyo]

Manual de referencia: DELETE Syntax

Sobre esa base, si miramos uno de los ejemplos que describe el manual:

Código SQL:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
 

Resulta evidente que para que te funcione es simplemente:

Código SQL:

DELETE V, A FROM vehiculo V INNER JOIN autos A ON V.matricula=A.matricula
    WHERE autos.matricula= matriculaBuscada
 

Debes tener en cuenta que MySQL no entiende variables de programación, por lo que sólo funcionará si al embeber el valor contenido den la variable, la sintaxis en SQL obtenida es correcta para MySQL.
Destaco eso porque muchas veces los programadores parecen suponer que MySQL puede "entender" variables de los lenguajes de programacion, y eso no es cierto.
En otros casos la mayoría de los errores es porque el programador supone que la variable contiene algo que realmente no contiene.

Como sea, prueba y nos dices.

Y, por favor, si devuelve un error, postea el mensaje de error que devuelve MySQL.
Decir que "me da error" no es información relevante ni significativa.

[Hyemin]

Iniciado por gnzsoloyo

Manual de referencia: DELETE Syntax



Resulta evidente que para que te funcione es simplemente:

Código SQL:

DELETE V, A FROM vehiculo V INNER JOIN autos A ON V.matricula=A.matricula
    WHERE autos.matricula= matriculaBuscada
 

Como sea, prueba y nos dices.

Y, por favor, si devuelve un error, postea el mensaje de error que devuelve MySQL.
Decir que "me da error" no es información relevante ni significativa.

Muchisimas gracias por tu ayuda, voy a estar probando combinaciones con esto, por ahora cuando quiero eliminar un registro usando:

Código MySQL:

DELETE V, A FROM vehiculo V 
INNER JOIN autos A ON V.matricula=A.matricula WHERE autos.matricula=AMD486;
 

Me da error Error Code: 1054. Unknown column 'autos.matricula' in 'where clause'

La columna matricula existe en la tabla autos al igual que existe con el mismo nombre en la tabla vehiculos

[gnzsoloyo]

En ese ejemplo tienes tres errores:

1) Si ya definiste alias para las tablas, usa los alias. Pueden darse errores de interpretación por parte del parser de MySQL que no son sencillos de diagnosticar. Acostúmbrate a usar los alias.

2) Asegurate siempre de los nombres de las columnas. MySQl te dice que en la tabla "AUTOS" no existe ninguna columna denominada "MATRICULA". Verifica bien el nombre antes de asumir que hay un error de MySQL (los errores son siempre del desarrollador, no del DBMS).

3) Ese valor que pones en el WHERE como perteneciente a una matrícula disparará un error de columna desconocida, porque como ya te dije, MySQL no entiende variables de los lenguajes de programación. Cuando creas dinámicamente una consulta, los valores de las variables deben entrar a la sentencia SQL como datos que el SQL reconozca como tales.
En esencia, si son números, van sin apóstrofos, pero si son alfanuméricos deben ir entre apóstrofos sí o si

Código MySQL:

DELETE V, A FROM vehiculo V INNER JOIN autos A ON V.matricula=A.matricula 
WHERE A.matricula='AMD486';
 

MySQL (y cualquier DBMS) tomará como nombre de un objeto de base de datos (tabla, columna, base, etc.) cualquier cosa que no sea una clausula y que comience por una letra. En ese contexto, MySQL asumirá que AMD486 es el nombre de una columna, a menos que lo pongas entre apóstrofos.

¿Se entiende?

[Hyemin]

O.O Gracias, ahora entiendo el error

De todos modos ahora el error que me da poniendo exactamente la query que me pasas es este:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`automotora`.`autos`, CONSTRAINT `autos_ibfk_1` FOREIGN KEY (`matricula`) REFERENCES `vehiculo` (`matricula`))

[gnzsoloyo]

Porque tienes una dependencia entre esa tabla y otras que usan ese dato como FK...
Estás intentando violar la restricción de integridad referencial, y el DBMS no te lo permitirá.
Cuando tienes una dependencia del tipo A <- B, donde la segunda poseen una FK que apunta a la primera, no puedes borrar la primera tabla sin previamente borrar la segunda.
En tu caso tu intención parece ser borrar ambas cosas al mismo tiempo, pero si lees atentamente el manual de referencia, un borrado multiple no necesariamente se realiza en la secuencia de dependencia.
Literalmente, el manua, en el lik que ya te pasé, dice:

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

lo cual significa:

Si utiliza un DELETE multitala en InnoDB para las que existen restricciones de clave foránea, el optimizador MySQL puede procesar tablas en un orden que difiere del de su relación padre / hijo. En este caso, el comando falla y la operación se deshace. En su lugar, debe eliminar de una sola tabla y basarse en la capacidad de ON DELETE que proporciona InnoDB para hacer que las otras tablas se modifiquen en consecuencia.

Esto significa que debes tener bien definido el ON DELETE CASCADE en las FK, par que al borrar la tabla padre únicamente, las otras tablas se borren en cascada.
Lo que hay que tener en cuenta es que no siempre ese es un buen método. Puede tener consecuencias cruzadas bastante nocivas si no se planea bien.

Este tipo de cosas siempre aparece en el manual de referencia. Por eso srecomendamos leer el manual con cuidado antes de preguntar nada. El 99% de los problemas se solucionan de esa forma. El resto es experiencia (que es lo único que hace que uno no necesite el manual).

[Hyemin]

Gracias nuevamente por tu rrespuesta, voy a estar leyendo eso entonces

Cuando elimino un registro de la tabla sin hacer la referencia la otra no se borra, de hecho en mi primer intento mi programa tenia dos querys para borrar las tablas por separado pero claro, tambien daba el error

[gnzsoloyo]

Cuando elimino un registro de la tabla sin hacer la referencia la otra no se borra, de hecho en mi primer intento mi programa tenia dos querys para borrar las tablas por separado pero claro, tambien daba el error

Eso muestra que tienes mal definida la clausula ON DELETE.
En cuanto a borrar con dos queries, la cosa es simple: Primer borras la que tiene la FK, y luego la tabla padre. Nunca al revés.
Ahora bien, si hacerlo con dos queries falla con la tabla hija, entonces hay dos posibilidades: 1) Esa tabla a su vez es FK en otra, es decir que hay otra tabla que depende de ella, o 2) Tienes una dependencia circular (poco probable).

En todo caso, lo que podrías hacer es mostrarnos qué te devuelve cada una de estas consultas:

Código SQL:

SHOW CREATE TABLE automotora;
 

Código SQL:

SHOW CREATE TABLE vehiculo;
 

Código SQL:

SHOW CREATE TABLE autos;
 

Eso nos puede mostrar cual es la dependencia entre esas tres tablas, aunque habría que verificar un poco mas para saber si hay otras transitivas que puedan bloquear borrados.

[Hyemin]

Código SQL:

CREATE TABLE `vehiculo` (
  `matricula` VARCHAR(60) NOT NULL,
  `marca` VARCHAR(60) DEFAULT NULL,
  `modelo` VARCHAR(60) DEFAULT NULL,
  `anio` INT(11) DEFAULT NULL,
  `precio_dia` INT(11) DEFAULT NULL,
  `disponible` tinyint(1) NOT NULL DEFAULT '1',
  `fecha_retorno` DATE DEFAULT NULL,
  PRIMARY KEY (`matricula`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1'
 

Código SQL:

CREATE TABLE `autos` (
  `idauto` INT(11) NOT NULL AUTO_INCREMENT,
  `puertas` tinyint(4) DEFAULT NULL,
  `gama` VARCHAR(12) DEFAULT NULL,
  `matricula` VARCHAR(60) DEFAULT NULL,
  PRIMARY KEY (`idauto`),
  KEY `matricula` (`matricula`),
  CONSTRAINT `autos_ibfk_1` FOREIGN KEY (`matricula`) REFERENCES `vehiculo` (`matricula`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1'
 

Automotora es la DB

PD: Gracias por todo el tiempo que te estas tomando

[gnzsoloyo]

Bueno, por lo pronto, sólo se ve que que AUTOS depende de VEHICULO, lo que sería bueno. La duda ahora es si AUTOS está siendo usada en otra tabla como FK, y para eso hay que hacer una consulta contra una base en especial, que sólo la usa internamente el MySQL (es una base kernel).
Veamos esto:

Código MySQL:

SELECT 
    REFERENCED_TABLE_NAME, TABLE_NAME
FROM
    information_schema.REFERENTIAL_CONSTRAINTS
WHERE
    LOWER(CONSTRAINT_SCHEMA) = 'automotora'
        AND LOWER(REFERENCED_TABLE_NAME) IN ('autos' , 'vehiculos');
 

Verifica qué te devuelve eso.

[Hyemin]

El comando me devuelve:

0 row(s) returned

[gnzsoloyo]

Puede ser porque estoy haciendolo un poco a ciegas. Yo no estoy trabajando en tu PC, por lo que puede haber errores de datos que no veo.
Como sea, el problema que tienes para ejecutar las queries es que no has definido la FK de modo que se borre en cascada, lo que genera ese problema para un borrado masivo o en orden incorrecto.
En ptincipio, haciendo dos queries debería funcionar:
Query 1:

Código MySQL:

DELETE autos 
WHERE matricula='AMD486';
 

Query 2:

Código MySQL:

DELETE FROM vehiculo 
WHERE matricula='AMD486';
 

De lo contrario hay que modificar la FK en la tabla para hacer que se pueda en una sola, para eso primero hay que borrar la FK que existe y luego crearla de nuevo, con las clausulas que faltan:

Código MySQL:

ALTER TABLE `autos`DROP FOREIGN KEY`autos_ibfk_1`;
 

Código MySQL:

ALTER TABLE autos ADD FOREIGN KEY (`matricula`) REFERENCES `vehiculo` (`matricula`) 
ON DELETE CASCADE ON UPDATE CASCADE;
 

Una vez hecho eso, deberías poder borrar la tabla padre sin peligro, y la hija se borraría en cascada.

Código MySQL:

DELETE FROM vehiculo 
WHERE matricula='AMD486';
 

[Hyemin]

Iniciado por gnzsoloyo

Puede ser porque estoy haciendolo un poco a ciegas. Yo no estoy trabajando en tu PC, por lo que puede haber errores de datos que no veo.
Como sea, el problema que tienes para ejecutar las queries es que no has definido la FK de modo que se borre en cascada, lo que genera ese problema para un borrado masivo o en orden incorrecto.


Una vez hecho eso, deberías poder borrar la tabla padre sin peligro, y la hija se borraría en cascada.

Código MySQL:

DELETE FROM vehiculo 
WHERE matricula='AMD486';
 

Ya quedó solucionado, al final puse dos comandos para borrar primero la tabla autos y luego vehiculos y ahi si no me dio el error.

Cuando lo habia hecho al reves si daba el error.

Quiero aprovechar para agradecerte muchisimo el tiempo que te has tomado en responder y que sepas que voloro mucho la calidad de la redacción de tus respuestas.

Aprendi en este post mucho más de lo que esperaba y te estoy agradecido

Si algun día publicas un manual de MySQL cuenta con mi compra