Ad:

MS SQL Discussion Thread
Unsolved
Views: 3197

Feb 11th, 2008

deleteting multiple rows and related table entries

Expand Post »

Hi there.

i would like this procedure to delete all rows in 'sale' where date_modified < (getdate() +0.02) and all the related rows from 'sale_detail' and 'sale_address' i have written the code below, but ti think this might only delete one row from sale and its related rows in the other tables. Is this correct or will it detele all entries from sale etc. If not any suggestions?

Thanks...

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE Sale_DeleteSaleTimedOut
 
AS
 
SET nocount ON
 
DECLARE
@sale_id int
 
 
     SELECT @sale_id = sale_id FROM sale WHERE date_modified < (getdate() +0.02)
     IF (@sale_id IS NOT NULL) AND (@sale_id <> '')
BEGIN
        DELETE sale_detail  WHERE sale_id = @sale_id
        DELETE sale_address WHERE sale_id = @sale_id
        DELETE sale WHERE sale_id = @sale_id
END
 
 
SET nocount off
CREATE PROCEDURE Sale_DeleteSaleTimedOut

AS

set nocount on

DECLARE
@sale_id int


     select @sale_id = sale_id from sale where date_modified < (getdate() +0.02)
     IF (@sale_id is NOT NULL) AND (@sale_id <> '')
BEGIN
        delete sale_detail  WHERE sale_id = @sale_id
        delete sale_address WHERE sale_id = @sale_id
        delete sale WHERE sale_id = @sale_id
END


set nocount off

jimshef

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2008

Permalink

Mar 7th, 2008

Re: deleteting multiple rows and related table entries

This will only work if it finds on result in the query "select @sale_id = sale_id from sale where date_modified < (getdate() +0.02)
"

It's better to do it with a cursor

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE Sale_DeleteSaleTimedOut
 
AS
 
SET nocount ON
 
DECLARE
@sale_id int
 
CREATE del_cursor cursor
FOR 
SELECT sale_id FROM sale WHERE date_modified < (getdate() +0.02) AND sale_id <> ''
 
open del_cursor
 
fetch next FROM del_cursor INTO @sale_id
 
while @@fetch_status <> 0
BEGIN
        DELETE sale_detail  WHERE sale_id = @sale_id
        DELETE sale_address WHERE sale_id = @sale_id
        DELETE sale WHERE sale_id = @sale_id
fetch next FROM del_cursor INTO @sale_id
 
END
 
close del_cursor
deallocate del_cursor
 
SET nocount off
CREATE PROCEDURE Sale_DeleteSaleTimedOut

AS

set nocount on

DECLARE
@sale_id int

create del_cursor cursor
for 
select sale_id from sale where date_modified < (getdate() +0.02) and sale_id <> ''

open del_cursor

fetch next from del_cursor into @sale_id

while @@fetch_status <> 0
BEGIN
        delete sale_detail  WHERE sale_id = @sale_id
        delete sale_address WHERE sale_id = @sale_id
        delete sale WHERE sale_id = @sale_id
fetch next from del_cursor into @sale_id

END

close del_cursor
deallocate del_cursor

set nocount off

Last edited by peter_budo; Mar 10th, 2008 at 9:26 pm. Reason: Keep It Organized - please use [code] tags

space1000

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

9 posts
since Mar 2008

Permalink

Mar 8th, 2008

Re: deleteting multiple rows and related table entries

Cursors are slooowwwwwww. you can add the ON DELETE CASCADE option to the foreign key in the sale_detail and sale_address tables. This makes it so that whenever you delete an entry in the parent table, all dependent records are deleted in the child tables that are created with this option. alternatively:

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE Sale_DeleteSaleTimedOut
AS
  BEGIN
  BEGIN TRANSACTION DeleteSaleTimedOut
 
  DELETE  FROM sale_detail
  FROM sale_detail AS d
  INNER JOIN sale AS s
    ON d.sale_id = s.sale_id
  WHERE s.date_modified < (getdate() +0.02)
 
 DELETE FROM sale_address
 FROM sale_address AS a
 INNER JOIN sail AS s
   ON a.sale_id = s.sale_ID
 WHERE s.date_modified < (getdate() +0.02)
 
 DELETE FROM sale AS s
 WHERE s.date_modified < (getdate() +0.02)
 
 COMMIT TRANSACTION DeleteSaleTimedOut
 END
CREATE PROCEDURE Sale_DeleteSaleTimedOut
AS
  BEGIN
  BEGIN TRANSACTION DeleteSaleTimedOut

  DELETE  FROM sale_detail
  FROM sale_detail AS d
  INNER JOIN sale AS s
    ON d.sale_id = s.sale_id
  WHERE s.date_modified < (getdate() +0.02)

 DELETE FROM sale_address
 FROM sale_address AS a
 INNER JOIN sail AS s
   ON a.sale_id = s.sale_ID
 WHERE s.date_modified < (getdate() +0.02)

 DELETE FROM sale AS s
 WHERE s.date_modified < (getdate() +0.02)

 COMMIT TRANSACTION DeleteSaleTimedOut
 END

almost anything you can do with cursors you can do with joins or subqueries or temporary tables and all of the above will be faster.

AaronASterling

Reputation Points: 10

Solved Threads: 5

Light Poster

Offline

31 posts
since Dec 2007

Permalink

Mar 8th, 2008

Re: deleteting multiple rows and related table entries

Hi there

thanks for the reply's I worked it out for myself befor anyone replied using the code below, i'm not sure which method will be the quickest? I'll run some tests on the database to have a look.

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE Sale_DeleteSaleTimedOut
 
AS
 
SET nocount ON
 
DECLARE
@sale_id int
 
WHILE EXISTS (SELECT sale_id FROM sale WHERE (date_modified+0.02) < getdate() )
    BEGIN
        SELECT @sale_id = sale_id FROM sale WHERE (date_modified+0.02) < getdate()
        IF (@sale_id IS NOT NULL) AND (@sale_id <> '')
            BEGIN
                DELETE sale_detail  WHERE sale_id = @sale_id
                DELETE sale_address WHERE sale_id = @sale_id
                DELETE sale WHERE sale_id = @sale_id
        End
    End
 
SET nocount off
CREATE PROCEDURE Sale_DeleteSaleTimedOut

AS

set nocount on

DECLARE
@sale_id int

WHILE EXISTS (SELECT sale_id from sale where (date_modified+0.02) < getdate() )
    BEGIN
        select @sale_id = sale_id from sale where (date_modified+0.02) < getdate()
        IF (@sale_id is NOT NULL) AND (@sale_id <> '')
            BEGIN
                delete sale_detail  WHERE sale_id = @sale_id
                delete sale_address WHERE sale_id = @sale_id
                delete sale WHERE sale_id = @sale_id
        End
    End

set nocount off

jimshef

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2008

Permalink

Mar 9th, 2008

Re: deleteting multiple rows and related table entries

Please post the results. I am curious too see which is fastest. thanks

AaronASterling

Reputation Points: 10

Solved Threads: 5

Light Poster

Offline

31 posts
since Dec 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: How do I Backup the MS SQL server 2000 database file (mdf) to my local PC ?

Next Thread in MS SQL Forum Timeline: (Sum(Sum(MarkU)))/5

DaniWeb Message

Cancel Changes

User Name
Password