CODEWITHSUNDEEP
mysqlsqlsql-delete
user1851950
user1851950

Reputation: 65

Delete in two tables gives errors

I must eliminate the technicians called "Oscar", who did repairs in the year 2010.

I must first delete the repairs they performed, then remove the technicians called "Oscar"'. I do this in two statements:

DELETE FROM Reparacion  WHERE cuit_tec IN(SELECT Tec.cuit
            FROM Tecnico Tec 
            WHERE Tec.nombre like 'oscar%'
            AND NOT EXISTS(SELECT 1 
                    FROM Reparacion Rep
                    WHERE Rep.cuit_tec = Tec.cuit 
                    AND Fecha BETWEEN '2010-01-01' AND '2010-12-31'));

and

DELETE FROM 
    Tecnico AS Tec 
WHERE 
    Nombre like 'oscar%' AND 
    NOT EXISTS (SELECT 
                1 
            FROM 
                Reparacion AS Rep 
            WHERE 
                Rep.Cuit_tec = Tec.Cuit AND 
                Fecha BETWEEN '2010-01-01' AND '2010-12-31');

This throws error messages:

"Error Code: 1093. You can not specify target table 'Repair' for update in FROM clause"
"Error Code: 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 'AS Tec WHERE Name = 'oscar' AND NOT EXISTS (SELECT 1 FROM R ' at line 2 "

sqlfiddle: http://sqlfiddle.com/#!2/9438d

Upvotes: 1

Views: 76

Answers (1)

Sergio
Sergio

Reputation: 6948

Isn't error here:

WHERE 
    Nombre like= 'oscar%' AND

Gotta be Nombre like 'oscar%' AND

Upvotes: 1

Related Questions