How do I delete rows from multiple tables?

[Guest_imported]

I have a problem deleting rows from multiple tables. I have 3 tables, tab1, tab2 and tab3. The tables are related, tab2 is foreignkey to primarykey in tab1. Tab2 is foriegnkey to tab3. (I try to visualise this below):

Tab3 <-- Tab2 --> Tab1

So, what I need to do is, delete all rows from the 3 tables that have a certain ID.

I need to send a parameter, for example 192 to a stored procedure, then this stored procedure should delete first the row in tab2 that has the ID 192, then it should delete the row in tab1 that has the ID 192 and finally delete the row from tab3 that has the ID 192. (I think it has to be done in that way, cause I cannot delete from tab1 first for example because it is primarykey to tab2).

It doesn't work for me like I've done the query, I get incorrect syntax error message:

CREATE PROCEDURE DeleteTest @arg INT AS
DELETE FROM tab1,tab2,tab3
WHERE tab2.[id]=@arg and tab1.[id]=@arg and tab3.[id]=@arg;

Hope someone can help me with this.

Thanks a lot!!

//Bob

 

[FLUZZI]

Hi Bob!

I think that the way you are doing your delete proceses is wrong. Try do this to your stored procedure:

CREATE PROCEDURE DeleteTest @arg INT
AS

BEGIN TRANSACTION

DELETE FROM tab3
WHERE tab3.[id]=@arg

DELETE FROM tab2
WHERE tab2.[id]=@arg

DELETE FROM tab1
WHERE tab1.[id]=@arg

COMMIT TRANSACTION

-- DELETE IN THIS ORDER!

 

[Guest_imported]

Thanks for your help Fluzzi!
I just solved this by using multiple:

DECLARE @cmd4 varchar(1000) ....

and...

SELECT @cmd1= 'SELECT event INTO TEMPTAB from [alarminfo alarmevent] where alarm='+@arg

etc.

It works fine now.

thanks for taking time! I love this place

//Bob

 

[Guest_imported]

Thanks for your help Fluzzi!
I just solved this by using multiple:

DECLARE @cmd4 varchar(1000) ....

and...

SELECT @cmd1= 'SELECT event INTO TEMPTAB from [tab2] where fire='+@arg

etc.

It works fine now.

thanks for taking time! I love this place

//Bob

 

[shyamMelwani]

hi bob,

I was just wondering and very curious as to what could be the requirement wherein the same field in tab2 had to be a foreign Key to two tables tab1 and tab3.

Ideally for a look-up detail relationship,
if both tab2 and tab3 are details, they should have had their corresponding fields foreign keyed to the look up tab1.

Thanks in advance

shyam

 

[Guest_imported]

Well, I have these 3 tables, table1, table2 and table3.

If I say, table1 is called CarInfo, table2 is called CarInfoCarEvent and table3 is called CarEvent (that's just some stupid names I just came up with, to give you a picture of this).

Well, table2 is actually not necessary, I've hada talk with the person who originally designed this database-system and he admits that table2 isn't necessary. It actually only shows the &quot;linked fields&quot; of table1 and table3.

It's something like that. It would be easier to show you

-Bob

 

[calahans]

Could you not set up the original primary table in the relationships to have a foreign constraint with the CASCADE DELETES set to true. When you delete the row from the tab2 then will delete the corresponding rows from the other tables.