Delete records from a few tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete records from a few tables

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-19 : 06:41:15

Hi,

I have a main table called Candidate that has the fields Candidate_id which is a primary key ,Given_name and Email.
In addition I have other tables that all have foreign keys to Candidate such as cand_seminar table with fields cand_sem_id (primary), candidate_id (foreign), seminar_date (datetime) etc.

So if i want to delete a candidate_id from Candidate table but that candidate_id also appears in other tables I have to first delete the foreign keys tables That is fine.

However, I need to delete at once multiple candidates that meet a certain condition such as where given_name='xxxx' from the main Candidate table as well as all other tables where the candidate_id appears.

I need a stored procedure that will looop through candidate_id that meets a certain condition in the Candidate table and then delete that candidate_id from all the tables.

How can i do that?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-19 : 07:07:28

You could define cascade deletes or do it in a trigger but (best imho).

create and populate a table #Candidate_IDs
begin try
begin tran
delete subtable1 from subtable1 join #Candidate_IDs on subtable1.Candidate_ID = #Candidate_IDs.Candidate_ID
delete subtable2 from subtable1 join #Candidate_IDs on subtable2.Candidate_ID = #Candidate_IDs.Candidate_ID
delete subtable3 from subtable1 join #Candidate_IDs on subtable3.Candidate_ID = #Candidate_IDs.Candidate_ID
delete maintbl from subtable1 join #Candidate_IDs on maintbl.Candidate_ID = #Candidate_IDs.Candidate_ID
commit tran
end try
begin catch
rollback tran
raiserror ('failed to delete', 16, -1)
return
end

Then it's just a matter of populating #Candidate_IDs.
You could put this processing in a stored proc and create and populate #Candidate_IDs in a calling proc if you want to make the criteria flexible.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-19 : 07:15:57

Hi

Thanks for the reply. Must I create a new table or a temp table and populate that table with the candidate ids that I need?

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-19 : 07:20:30

You don't have to - you can call the SP with individual IDs or a csv string or repeate the sql to get the values in every delete - the temp table will be easier and isolates the criteria.
Makes it more flexible and testing easier.
Also means that findnig the IDs is outside the transaction (unless you want to lock the tables when you get them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-10-19 : 14:04:12

Hi nr,

Thanks for the reply. I am not sure that I understand how to do what you said

. I understand from you latest posting that i should call the sp with individual ids but how do i do that? I am sorry but coz of my lack of experience I don't understand how to proceed with your suggestion.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-20 : 17:24:05

>> I need a stored procedure that will looop through candidate_id that meets a certain condition in the Candidate table

Use that condition to get the IDs
create #Candidate_IDs (Candidate_ID int)
insert #Candidate_IDs
select Candidate_ID
from MainTbl
where ......

Then call the Sp above
You could embed this code in the SP if you wish
Alternatively
declare @Candidate_ID int
select @Candidate_ID = 0
while @Candidate_ID < (select max(Candidate_ID) from#Candidate_IDs)
begin
select @Candidate_ID = min(Candidate_ID) from #Candidate_IDs where Candidate_ID > @Candidate_ID
begin
exec s_delCandidate @Candidate_ID
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources