Delete rows in my db with mySQL

Question

This is what my DB looks like:

enter image description here

I am trying to delete all the records from both these tables where ID = 8.
This is what I've tried:

DELETE from users1,pontaj WHERE users1.id, pontaj.id  IN(8)

Ravinder Reddy · Accepted Answer · 2014-04-15 00:23:26Z

1

If you had defined potaj table with pontaj.id as

CREATE TABLE pontaj (
  id int, foreign key(id) references users1(id)
         on delete cascade on update cascade
)

then just executing

DELETE from users1 WHERE users1.id IN(8)

would have deleted records from both parent and child tables.

edited Apr 15, 2014 at 0:23

answered Apr 15, 2014 at 0:18

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ExtremeSwat Over a year ago

Sorry for the stupid question, but If I run this as a SQL command, I get an error. Should I delete pontaj table, then run the query ?

Ravinder Reddy Over a year ago

@ExtremeSwat: No. If users1.id has an index defined on it, this solution should work. Otherwise define a key(id) in users1 tables. But your images map shows that the referred field is a primary key and hence it should have worked without any issue.

ExtremeSwat Over a year ago

No effect whatsoever over the other 2 tables.The command just deletes the id from the 1'st table hmm...

Ravinder Reddy Over a year ago

@ExtremeSwat: Perhaps the child fields are defined with on delete set null.

Ravinder Reddy Over a year ago

@ExtremeSwat: Run show create table pontaj; and show create table salariu; and see how the child columns are defined as.

|

Collectives™ on Stack Overflow

Delete rows in my db with mySQL

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related