Help no Delete

Question

Post reply

Help no Delete

Shahed Rasheed

Old Hand

Points: 398
More actions
May 12, 2005 at 3:06 am

#68994

Hi guys,

Greetings from India!!!

I need some help on a delete operation

My scenario:

I have two tables A,B. I need to first find out which records to delete in A based on A.ID ,and then delete related records from both A, B

Is it possible to execute delete on multiple tables from a single statement? I did some googling and some guy @ a forum said this wasnt possible. Just want to make sure if this is indeed not possible.

Assuming u cant do it from a single query, i decided to go for the foll. Stored proc:

I first get all Ids to delete and store it a local variable

Select @Ids = @Ids + ',' + Id from A where.....(neat trick, heh 😉 )

So my @Ids now looks something like '1002,1003,1004'

I then execute multiple deletes like this-

Set @Sql = 'Delete from A where Id in (' + @Ids +');'

Set @Sql = @Sql + 'Delete from B where Id in (' + @Ids +')'

Execute sp_executesql @Sql

My problem is that my local variable, @Ids is limited to only 8000 chars. What if my @Ids supposed to have 1000 numbers of 5 digits each, then wont my @Ids get truncated?

I was also thinking abt creating a temp table to stored my Ids. In that case, the above problem wont occur? But i am concerned abt any performance drag.

Can someone help me as to which approach to take?

Thanks in advance,

Shahed

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

bersileus sacamay-126226 SSChasing Mays Points: 642 More actions · Answer 1

bersileus sacamay-126226

SSChasing Mays

Points: 642

May 12, 2005 at 3:12 am

#558302

if the tables are in a Referential Integrity with each other, just deleting from the parent table, will casade the delete to the parent & child table(s). just issue one delete command. gone. whack, pow!, zapped

David Perry-232147 Valued Member Points: 51 More actions · Answer 2

Hi, You could set up a delete trigger on the parent table which deletes related records in the child.

I use this method when i can't use the built in referential integrity: for example when there may me many rows in the parent table with the same ID and i want to delete children only when the last row of any specific parent is deleted.

here's an example:

CREATE TRIGGER t_DeleteLinksDT_APPRAISAL ON [dbo].[DT_APPRAISAL]

FOR DELETE

AS

CREATE TABLE #MyTempTable (Entity_Id INT PRIMARY KEY)

INSERT INTO #MyTempTable (Entity_ID) (SELECT DISTINCT Deleted.Entity_ID FROM Deleted LEFT OUTER JOIN DT_APPRAISAL ON Deleted.Entity_ID = DT_APPRAISAL.Entity_Id WHERE DT_APPRAISAL.Entity_Id IS NULL )

DELETE FROM LNK_APPRAISAL_APPRAISOR WHERE Left_Join IN (SELECT Entity_Id FROM #MyTempTable)

DROP TABLE #MyTempTable

---

Dave

Shahed Rasheed Old Hand Points: 398 More actions · Answer 3

Shahed Rasheed

Old Hand

Points: 398

May 12, 2005 at 5:32 am

#558328

Thanx Dave for that tip. But is it still possible to do all this in one single query ???

David Perry-232147 Valued Member Points: 51 More actions · Answer 4

David Perry-232147

Valued Member

Points: 51

May 12, 2005 at 5:43 am

#558330

You mean a single line of SQL? I don't think so

I think the delete trigger method is better than writing a sp as it runs automaticaly. It also gives you access to a special Deleted temporary table so you don't have to create your long vchar to use in your IN() filter.