deleteting multiple rows and related table entries

[jimshef]

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...

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

[space1000]

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

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

[AaronASterling]

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:

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.

[jimshef]

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.

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

[AaronASterling]

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