delete from two tables based on FK
I have two tables, tblOne (firstid (PK), secondid (FK)
tblTwo (secondid (PK), name, address)
I'm looking to create a DELETE statement that will delete from tblOne where firstid = @firstid, but then subsequently DELETE the related record from tblTwo based on the tblOne.secondid value of the deleted record. Can this be done in one statement?
tblTwo (secondid (PK), name, address)
I'm looking to create a DELETE statement that will delete from tblOne where firstid = @firstid, but then subsequently DELETE the related record from tblTwo based on the tblOne.secondid value of the deleted record. Can this be done in one statement?
AliSyed
You can set delete cascase and and it will be done automatically.
DELETE CASCADE
look at this
look at this
Not in one statement but can be done in the same batch in a transaction. Cascaded deletes in the other way, is not what you are after
Is it always one to one? Otherwise the delete from tbl2 may fail due to other records using that id.
ASKER
Yes, always one to one
ASKER
You say that CASCADE delete goes the other way, ie del tblTwo record first and then the associated tblOne record? If so, I think that would be okay
Then you can set up the foreign key to be the "master" of the relationship and cascade deletes, updates. Even then one statement may not be enough, if you have records in tblOne that have no secondid link.
alter table tblOne add constraint FK_onetwo foreign key
REFERENCES tblTwo(secondid) ON DELETE CASCADE ON UPDATE CASCADE;
Your statement:
Delete tblTwo where secondid in (select secondid from tblOne where <some condition>)
This deletes from tblTwo and will grab all the records from tblOne that link by FK, however, if there are records in tblOne that are not linked to tblTwo, you will need to fire a secondary delete
delete tblOne where <some condition>
So it may or may not be better than just managing the deletes in a batch transaction.
alter table tblOne add constraint FK_onetwo foreign key
REFERENCES tblTwo(secondid) ON DELETE CASCADE ON UPDATE CASCADE;
Your statement:
Delete tblTwo where secondid in (select secondid from tblOne where <some condition>)
This deletes from tblTwo and will grab all the records from tblOne that link by FK, however, if there are records in tblOne that are not linked to tblTwo, you will need to fire a secondary delete
delete tblOne where <some condition>
So it may or may not be better than just managing the deletes in a batch transaction.
ASKER
BEGIN TRAN
SET @secondid = tblOne.secondid WHERE firstid = @firstid
DELETE FROM [tblOne] WHERE [firstid] = @firstid
DELETE FROM [tblTwo] WHERE [secondid] = @secondid
If @@error = 0
COMMIT TRAN
ELSE
ROLLBACK
Incorrect syntax near the keyword 'WHERE'
SET @secondid = tblOne.secondid WHERE firstid = @firstid
DELETE FROM [tblOne] WHERE [firstid] = @firstid
DELETE FROM [tblTwo] WHERE [secondid] = @secondid
If @@error = 0
COMMIT TRAN
ELSE
ROLLBACK
Incorrect syntax near the keyword 'WHERE'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That is if you are always deleting just one record, I thought you wanted something more general like batch deletes.
ASKER
Oh yes, always deleting just one record. Working well now, thank you sir