delete con inner join

[jucas1987]

Buen dia.

Sera que me pueden decir por que no me funciona esta instruccion sql?

delete from tbllogrosxgrado
inner join tbllogros on tbllogrosxgrado.id_logro=tbllogros.id_logro
where identificacion='1036606448'

ya llevo tiempo usando el inner join, pero es la primera vez que lo empleo en un delete y no me ha querido funcionar.

me dice esto:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inner join tbllogros on tbllogrosxgrado.id_logro=tbllogros.id_logro where ident' at line 2

Les agradezco de antemano por su atencion.

[huesos52]

Que yo sepa, mysql no soporta inner join en sentencias delete.
Haz uso de on delete cascade y on update cascade.

saludos

[gnzsoloyo]

En realidad, si se puede usar, según dice el manual de referencia (13.2.1. Sintaxis de DELETE ), por lo que supongo que el problema puede venir de otra parte.
La sentencia que usas es (reescribiendo):

Código sql:

DELETE FROM tbllogrosxgrado
            INNER JOIN tbllogros USING(id_logro)
WHERE identificacion='1036606448';
 

No se ve ningún error. ¿Este es un ejemplo real, o está implementado de otra forma? Por ejemplo, con el valor en una variable...

[huesos52]

Ni en la versión 5 ni en la 5.1 me ha funcionado. gnzsoloyo en el link que pones no hacen uso en ninguna parte de la sentencia inner join, sino de delete con múltiples tablas. esto se muestra como un error de sintaxis.

La parte table_references lista las tablas involucradas en el join. Esta sintaxis se describe en Sección 13.2.7.1, “Sintaxis de JOIN”.

Para la primera sintaxis, sólo los registros coincidentes de las tablas listadas antes de la cláusula FROM se borran. Para la segunda sintaxis, sólo los registros coincidentes de las tablas listadas en la cláusula FROM (antes de la cláusula USING ) se borran. El efecto es que puede borrar registros para varias tablas al mismo tiempo y tienen tablas adicionales que se usan para buscar:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
O:

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
Estos comandos usan las tres tablas al buscar registros a borrar, pero borrar los registros coincidentes sólo para las tablas t1 y t2.

Los ejemplos anteriores muestran inner joins usando el operador coma, pero comandos DELETE de varias tablas pueden usar cualquier tipo de join permitido por comandos SELECT tales como LEFT JOIN.

Aunque diga que permite multiples sentencias join, siempre genera error de sintaxis al tratar de hacerlo como propones.

La forma como me ha funcionado (en parte) es:

Código sql:

DELETE tblg,tbl FROM tbllogrosxgrado tblg,tbllogros tbl WHERE tblg.id_logro=tbl.id_logro
 

El problema es que elimina todos los registros que hayan entre las tablas. si le pongo la condición and identificacion='1036606448';
saca un error de integridad referencial.

Insisto en el uso de on delete cascade

saludos

[gnzsoloyo]

Estoy de acuerdo en que un ON DELETE CASCADE es muchísimo más eficiente. Lo que yo quería destacar es que según el manual de referencia ese DELETE debería funcionar, ya que, también según el manual:

Puede especificar múltiples tablas en un comando DELETE para borrar registros de una o más tablas dependiendo de una condición particular en múltiples tablas. Sin embargo, no puede usar ORDER BY o LIMIT en un DELETE de múltiples tablas.

La parte table_references lista las tablas involucradas en el join. Esta sintaxis se describe en Sección 13.2.7.1, “Sintaxis de JOIN”.

y es la sección 13.2.7.1 dice:

MySQL soporta las siguientes sintaxis de JOIN para la parte table_references de comandos SELECT y DELETE y UPDATE de múltiples tablas:

table_reference, table_reference
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ ON table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Y además:

INNER JOIN y , (coma) son semánticamente equivalentes en la ausencia de una condicicón de join: ambos producen un producto Cartesiano entre las tablas especificadas (esto es, cada registro en la primera tabla se junta con cada registro en la segunda tabla).

Por ello mi duda es que si dicen que debería funcionar, ¿por qué razón ni tu ni él han logrado que esto funcione?

Googleando, he encontrado este caso, en que se muestra la ocurrencia de ese DELETE: http://www.java2s.com/Tutorial/MySQL...TableJoins.htm

[huesos52]

Si.. el manual es claro y estoy deacurdo que debería funcionar.
A ti te funciona gnzsoloyo?


En el link que acabas de poner funciona, pero ten en cuenta que las tablas no están relacionadas. No existen claves primarias ni foráneas.

[gnzsoloyo]

Bien, finalmente conseguí en una página que tenía ejercicios sobre DELETE en MySQL un script de prueba para el caso, y lo probé.
Funcionó tras unas modificaciones.

El tema pasa por establecer el orden de borrado, y definir específicamente qué tabla es la que se va a borrar, ya que el DELETE puede abarcar todas las tablas, algunas o una sola de las involucradas
El script que usé es este:

Código sql:

DROP TABLE IF EXISTS AuthorArticle;
DROP TABLE IF EXISTS Articles;
DROP TABLE IF EXISTS Authors;
 
 
 
CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;
 
 
INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
                            (13331, 'Publish a paper', 1919),
                            (14356, 'Sell a paper', 1966),
                            (15729, 'Buy a paper', 1932),
                            (16284, 'Conferences', 1996),
                            (17695, 'Journal', 1980),
                            (19264, 'Information', 1992),
                            (19354, 'AI', 1993);
 
 
CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthorFirstName VARCHAR(20),
   AuthorMiddleName VARCHAR(20),
   AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;
 
 
INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
                           (1007, 'Jason', 'Carol', 'Oak'),
                           (1008, 'James', NULL, 'Elk'),
                           (1009, 'Tom', 'M', 'Ride'),
                           (1010, 'Jack', 'K', 'Ken'),
                           (1011, 'Mary', 'G.', 'Lee'),
                           (1012, 'Annie', NULL, 'Peng'),
                           (1013, 'Alan', NULL, 'Wang'),
                           (1014, 'Nelson', NULL, 'Yin');
 
 
CREATE TABLE AuthorArticle (
   AuthID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, ArticleID),
   FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;
 
 
INSERT INTO AuthorArticle VALUES (1006, 14356), 
                              (1008, 15729),
                              (1009, 12786),
                              (1010, 17695),
                              (1011, 15729),
                              (1012, 19264),
                              (1012, 19354),
                              (1014, 16284);
 

En este ejemplo existe una dependencia entre la tabla AuthorArticle y las otras dos, por lo cual para eliminar cualquiera de los registros ubicados en Author y Article, primero hay que eliminar el registro donde se vinculan en AuthorArticle.
Para lograrlo no podemos hacer:

Código sql:

DELETE  
FROM Authors a INNER JOIN AuthorArticle ab USING(AuthID)
   INNER JOIN Articles b USING(ArticleID)
WHERE AuthorLastName='Elk';
 

porque eso implicaría que los registros se borren de las tablas en esta secuencia: Authors -> AuthorArticle -> Article , lo que genera un problema de restricción de FK.
Para lograr que se borren, hay que establecer la secuencia de otra forma: AuthorsArticle -> Author -> Article.
Esto sería:

Código sql:

DELETE ab, b 
FROM Authors a INNER JOIN AuthorArticle ab USING(AuthID)
   INNER JOIN Articles b USING(ArticleID)
WHERE AuthorLastName='Elk';
 

y luego eliminar el tercer registro en la primera tabla:

Código sql:

DELETE a
FROM Authors a;
 

(todavía no analicé por qué no me deja borrar todos juntos).

En el caso debería funcionar con:

Código sql:

DELETE lg 
FROM tbllogrosxgrado lxg
            INNER JOIN tbllogros lg USING(id_logro)
WHERE lxg.identificacion='1036606448';