Delete rows from multiple tables based on a single input

Question

I have three tables:

estate_field_data
estate_field_types
estate_fields

And the only input i have from the browser is estate_field_types.ID = 3

estate_fields contains a definition for estate_field_types.ID => estate_fields.FieldType

But estate_field_data does not have a definition for the estate_field_types.ID, but instead a estate_field_data.FieldID => estate_fields.ID

How can I delete from all three rows with this single value?

Thank you! :-)

Damien_The_Unbeliever · Accepted Answer · 2011-06-16 08:58:43Z

3

You have to do it as 3 separate statements (unless you've set up cascading deletes on your foreign key references). I'm assuming there's a PK in estate_fields (I've chosen to call it estate_field_id) which estate_field_data references (otherwise, I'm unsure how we identify what to delete from that table)

delete from estate_field_data where estate_field_id in (select estate_field_id from estate_fields where FieldType = 3)

delete from estate_fields where FieldType = 3

delete from estate_field_types where ID = 3

answered Jun 16, 2011 at 8:58

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

1 Comment

janhartmann Over a year ago

Exactly! It was the "IN"-clause that was missing! :-) Thank you!

Pankaj · Accepted Answer · 2011-06-16 09:13:35Z

-1

Try with joins as well.... Damien ... Should we suggest OP for Joins usage? May be quicker in OP case?

Delete K From estate_field_data K
Inner Join estate_fields F on F.estate_field_id = K.estate_field_id
Where F.estate_field_id = 3

answered Jun 16, 2011 at 9:13

Pankaj

10.2k40 gold badges152 silver badges298 bronze badges

6 Comments

janhartmann Over a year ago

Should the estate_field_types then be deleted in a second statement?

Pankaj Over a year ago

rest you can follow Damien's suggestion.... But I want to suggest Joins in place of In statement.. Will be quicker in deletion....

Damien_The_Unbeliever Over a year ago

@meep - you'd still need to do 3 separate deletes - there's no way for a delete (or insert/update) to affect more than one table (directly - such a statement against a table with triggers or cascading deletes on foreign keys may end up affecting multiple tables)

Damien_The_Unbeliever Over a year ago

I wouldn't expect any speed difference. Both queries would need to access the estate_field_id of estate_fields, with a filter criteria against FieldType, and both queries need to filter estate_field_data based on estate_field_id - the same indexes would assist both queries (or the lack of indexes would hinder both queries)

janhartmann Over a year ago

I'd stick with Damien. But nice to learn various ways of doing this.

|

Collectives™ on Stack Overflow

Delete rows from multiple tables based on a single input

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related