DELETE WITH LEFT JOIN

Asked

Viewed 666 times

2

I’m having an error trying to execute this command:

DELETE doacoes FROM doacoes LEFT OUTER JOIN animais ON animais.idAnimal = doacoes.idAnimalDoacao WHERE animais.idAnimal = doacoes.idDoacao;

I want to delete one DONATION, where the ANIMAL is deleted at the same time.

You keep making that mistake:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
mysql sql join

  • This is happening in mysql. How would the code?

  • 1

    How is the relationship between the donation tables and animals? The clauses UPDATE CASCADE and DELETE CASCADE would not meet your needs?

  • It’s because I’m new to this area. I have the Animal and Donation table. When I add an animal, the animal ID goes to the DOACAO table as idAnimalDoacao, thus creating a donation

  • What database are you using? Mysql or SQL Server? In the comment you referred to Mysql but your question is marked as sql-server, correct the information!

  • 1

    I am using Mysql. Sorry

2 answers

2


In your SQL you are referencing the key fields in the clause ON and again at WHERE.

Try to put in the WHERE some validation as for example

DELETE doacoes, animais 
  FROM doacoes 
 INNER JOIN animais ON animais.idAnimal = doacoes.idAnimalDoacao 
 WHERE doacoes.idDoacao = 13

  • With this one, you’re making a mistake at FK

  • I modified the answer, try again. Note: I am without access to Mysql now.

  • I was able to delete donations, animals FROM donations INNER JOIN animals ON animals.idAnimal = donations.idAnimalDoacao WHERE donations.idDoacao = 13

  • Wonder! I had reversed the tables of where unintentionally, I will adjust the answer.

0

Must be in mysql safe mode try to run this.

SET SQL_SAFE_UPDATES = 0;
DELETE doacoes,animais FROM doacoes INNER JOIN animais WHERE animais.idAnimal = doacoes.idDoacao;

  • Did not delete, but the code executed

  • edited, try again with this above, in case you don’t test using INNER JOIN instead of LEFT OUTER JOIN

  • It didn’t work :/ .

Browser other questions tagged mysql sql join

You are not signed in. Login or sign up in order to post.