CODEWITHSUNDEEP
t-sqlsql-server-2008stored-procedures
janhartmann
janhartmann

Reputation: 15003

Delete rows from multiple tables based on a single input

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! :-)

Upvotes: 2

Views: 3448

Answers (2)

Pankaj
Pankaj

Reputation: 10095

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

Upvotes: -1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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

Upvotes: 3

Related Questions