Deleting rows of two related tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting rows of two related tables

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-24 : 03:34:02

Hi,

I want to delete some rows of two linked tables by the columns ‘Offer_quality_id’ (Identity in the first table) and ‘Offer_quality_num’ (Foreing Key in the second table):

CREATE TABLE [Offer_quality] (
[Offer_quality_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Offer_num] [bigint], (FOREIGN KEY) linked to ‘Offers’ main table
[Quality_num] [smallint], (FOREIGN KEY) (Q, QA, QM or QB)
[Caliber] [numeric](6, 2) NULL ,
[Measure_Caliber] [smallint] NULL , (FOREIGN KEY)
[Kind_Caliber] [smallint] NULL , (FOREIGN KEY)
[Image_name] [varchar] (256) COLLATE Modern_… NULL ,
[Directory_path_num] [int] NULL , (FOREIGN KEY)
[Transport_cost] [smallint] NULL , (FOREIGN KEY),

CREATE TABLE [Offer_ripening] (
[Offer_ripening_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Offer_quality_num], (FOREIGN KEY) linked to ‘Offer_quality’ table
[Ripening_grade] [smallint], (FOREIGN KEY) (MA, MM, MB or null)
[Available_quantity] [numeric](8, 2) NULL,
[Measure_quant] [smallint] NULL, (FOREIGN KEY)
[Kind_container] [smallint] NULL , (FOREIGN KEY)
[Quant_container] [numeric](8, 2) NULL ,
[Measure_quantCont] [smallint] NULL , (FOREIGN KEY)
[Price] [numeric](8, 2) NULL ,
[Measure_Price] [smallint] NULL , (FOREIGN KEY),

Now I delete the rows for a specified ‘Offer_num’ (Offer_num column in first table) thus:


USE market
GO
ALTER PROC del_offer
@Offer_id bigint
AS

SET NOCOUNT ON

   DELETE FROM Offer_ripening
   WHERE Offer_quality_num IN (SELECT Offer_quality_id
   FROM Offer_quality
   WHERE Offer_num = @Offer_id)       
     
   DELETE FROM Offer_quality
   WHERE Offer_num = @Offer_id  
        
SET NOCOUNT OFF
GO

Is this a correct way to do it? Or is it better delete the rows of the second table ‘Offer_ripening’ by simply setting the first table to ‘Cascade Delete Related Records’? And thus the SP only would be:


USE market
GO
ALTER PROC del_offer
@Offer_id bigint
AS

SET NOCOUNT ON
     
   DELETE FROM Offer_quality
   WHERE Offer_num = @Offer_id  
        
SET NOCOUNT OFF
GO

Thank you,
Cesar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources