?
Solved

delete from two tables based on FK

Posted on 2010-11-15
11
Medium Priority
?
471 Views
Last Modified: 2012-05-10
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
Comment
Question by:alright
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 6

Expert Comment

by:AliSyed
ID: 34138775
You can set delete cascase and and it will be done automatically.
0
 
LVL 6

Expert Comment

by:AliSyed
ID: 34138792
DELETE CASCADE
look at this
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34138801
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34138837
Is it always one to one? Otherwise the delete from tbl2 may fail due to other records using that id.
0
 

Author Comment

by:alright
ID: 34138857
Yes, always one to one
0
 

Author Comment

by:alright
ID: 34138865
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34138926
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
 

Author Comment

by:alright
ID: 34138929
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34138970
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34138976
That is if you are always deleting just one record, I thought you wanted something more general like batch deletes.
0
 

Author Closing Comment

by:alright
ID: 34139040
Oh yes, always deleting just one record. Working well now, thank you sir
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question