Help no Delete

Question

Help no Delete

Shahed Rasheed

SSC-Addicted

Points: 458
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

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 SSC-Addicted Points: 458 More actions · Answer 3

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

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

DELETE only works on one table at a time.

And I think there are some very good reasons for this.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Care to elaborate on those reasons Frank?? Can't think of many right now.

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

What about if you created an updatable view with ALL of the fields from both tables. If there was a 1-1 relationship wouldn't that work?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Once one has deleted more than one wanted to, I could think of many good reasons. Especially in dynamic environments

Actually I think the only reason is, that it syntactically not allowed.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Shahed Rasheed SSC-Addicted Points: 458 More actions · Answer 9

Thanx Frank,

Can u tell me how this Referential Integrity is implemented, as Bersileus mentioned?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

BOL can do this better than me. You might want to start reading at "cascading referential integrity constraints"

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]