MS SQL

deleteting multiple rows and related table entries

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Feb 2008

Posts: 2

Reputation: jimshef is an unknown quantity at this point

Solved Threads: 0

jimshef

Offline

Newbie Poster

deleteting multiple rows and related table entries

Feb 11th, 2008

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

 Help with Code Tags 
 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

•

Join Date: Mar 2008

Posts: 9

Reputation: space1000 is an unknown quantity at this point

Solved Threads: 1

space1000

Offline

Newbie Poster

Re: deleteting multiple rows and related table entries

Mar 7th, 2008

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

 Help with Code Tags 
 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

•

Join Date: Dec 2007

Posts: 31

Reputation: AaronASterling is an unknown quantity at this point

Solved Threads: 5

AaronASterling AaronASterling is offline

Offline

Light Poster

Re: deleteting multiple rows and related table entries

Mar 8th, 2008

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:

 Help with Code Tags 
 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.

Aaron Sterling

•

Join Date: Feb 2008

Posts: 2

Reputation: jimshef is an unknown quantity at this point

Solved Threads: 0

jimshef

Offline

Newbie Poster

Re: deleteting multiple rows and related table entries

Mar 8th, 2008

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.

 Help with Code Tags 
 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