Post reply

Delete Query based on 2 tables-DEADLINE Looming

  • November 23, 2004 at 4:12 pm

    #59739

    I am trying to create a delete query that will delete data from 1 table based on the data in table 2.

    DELETE FROM [SD_Order_Dev].[dbo].[ProjectDemand]

    WHERE dbo.CADFilePath.ProjNum=dbo.ProjectDemand.ProjectNumber

    What is the proper syntax - I am trying to call this query from DTS Execute Sql Task.

    Need Urgent response - DEADLINE Looming

    Thanks for your assistance,

    Karen

  • November 23, 2004 at 4:41 pm

    #531326

    Hi Karen

    This should work:

    DELETE FROM [SD_Order_Dev].[dbo].[ProjectDemand]

    WHERE

    dbo.ProjectDemand.ProjectNumber in

    (select ProjNum from dbo.CADFilePath)

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 23, 2004 at 8:35 pm

    #531338

    Hi,

    I think we don't need to give full name for the column names. so we can give

    DELETE FROM [SD_Order_Dev].[dbo].[ProjectDemand]

    WHERE ProjectNumber IN (SELECT Projnum FROM [dbo].[CADFilepath]

    Also when you use Execute Sql task in DTS you can check whether the syntax is correct using the Parse query button in the Execute Sql task.

    Thanks,

    Sridhar!!

  • November 24, 2004 at 9:09 am

    #531421

    I have read a few different ideas on whether to use subqueries such as what are displayed here for deleting (or updating) between two tables.

    In your experiences what is normally the best idea for this?  Or is it simply easier to write the subqueries than doing it via inner joins?

  • November 24, 2004 at 9:36 am

    #531429

    I think it's mainly personal preference, but I usually write out the JOIN so that it's more immediately obvious what's hapening in the query.

    DELETE dbo.ProjectDemand

    FROM dbo.ProjectDemand pd

    JOIN dbo.CADFilePath cfp ON pd.ProjectNumber = cfp.ProjNum

    Marbry

  • November 24, 2004 at 9:39 am

    #531432

    thanks everyone for their suggestions.

    Karen

  • November 24, 2004 at 4:59 pm

    #531487

    I actually think that the JOIN method will execute faster than the subquery I proposed. However, the subquery method is easier IMO to write and I had a hangover when I responded, so I took the easy way out!

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply