• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 482
  • Last Modified:

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?
0
alright
Asked:
alright
  • 5
  • 4
  • 2
1 Solution
 
AliSyedCommented:
You can set delete cascase and and it will be done automatically.
0
 
AliSyedCommented:
DELETE CASCADE
look at this
0
 
cyberkiwiCommented:
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
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
cyberkiwiCommented:
Is it always one to one? Otherwise the delete from tbl2 may fail due to other records using that id.
0
 
alrightAuthor Commented:
Yes, always one to one
0
 
alrightAuthor Commented:
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
0
 
cyberkiwiCommented:
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.
0
 
alrightAuthor Commented:
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'
0
 
cyberkiwiCommented:
BEGIN TRAN
select @secondid = secondid from tblOne where firstid = @firstid
DELETE FROM tblTwo where secondid = @secondid
DELETE FROM [tblOne] WHERE [firstid] = @firstid
If @@error = 0
      COMMIT TRAN
ELSE
      ROLLBACK
0
 
cyberkiwiCommented:
That is if you are always deleting just one record, I thought you wanted something more general like batch deletes.
0
 
alrightAuthor Commented:
Oh yes, always deleting just one record. Working well now, thank you sir
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now