none
DELETE Rows From a Stage Table RRS feed

  • Question

  • Good afternoon once again, all -

    My, I'm being a pest this week.

    This question is not so much a problem, but a reaching out for clarification.

    I've been given an SSIS solution to update for the inclusion of a new table. I'll be using another package in the solution as a template for the development of this new one.

    The existing package is very simple. It reads the new monthly data into a staging table, looks for rows in the new data that are updates to those in the production table, deletes those rows that match from the production table and then inserts the stage table's contents into it.

    All well and good and quite simple. I've done similar things in the past with little effort. But, this particular package was designed by someone else and the SQL task they use to check for updated rows is a little bit different than what I'm familiar with.

    This is the existing query - which, by the way, works, but it just has a few differences from what I'm familiar with;

    DELETE Prod_table
    from  Prod_table a
       inner join Stage_table b
          on a.Date_Group = b.Date_Group
             and a.Date_of_Service = b.Date_of_Service
             and a.Claim_Number = b.Claim_Number
             and a.Claim_Line_Number = b.Claim_Line_Number
             and a.Mbr_ID = b.Mbr_ID

    Now, I pretty much see what this is doing, but shouldn't there be a WHERE clause in there? And, having the Prod_Table immediately after the DELETE statement and after the FROM is confusing to me. I'd expect it to be DELETE FROM Prod_Table …, not DELETE Prod_Table FROM Prod_Table….

    I'm guessing that the JOINs serve the same purpose as the WHERE clause would, but it looks odd with the WHERE missing - and, recall, this is a working query, to the best of my knowledge.

    So, like I said, I'm not looking for any sort of solution to a problem, but just some clarity on what is happening.

    Thanx in advance for any assistance!

    Wednesday, December 5, 2018 7:59 PM

Answers

  • The DELETE statement looks good to me

    the Prod_table after DELETE represents table to be deleted

    The FROM part includes logic based on which DELETion has to be happen which is where you've the join syntax between production and Staging based on those columns

    So what you've is a valid syntax. You can write it in other ways too

    like for example

    1. using aliases rather than repeating table names

    like

    DELETE a
    from  Prod_table a
       inner join Stage_table b
          on a.Date_Group = b.Date_Group
             and a.Date_of_Service = b.Date_of_Service
             and a.Claim_Number = b.Claim_Number
             and a.Claim_Line_Number = b.Claim_Line_Number
             and a.Mbr_ID = b.Mbr_ID

    2. Using EXISTS

    DELETE a
    from  Prod_table a
    WHERE EXISTS (
             SELECT 1
             FROM Stage_table b
             WHERE a.Date_Group = b.Date_Group
             and a.Date_of_Service = b.Date_of_Service
             and a.Claim_Number = b.Claim_Number
             and a.Claim_Line_Number = b.Claim_Line_Number
             and a.Mbr_ID = b.Mbr_ID
            )

    etc

    All of these are equivalent statements and works the same way


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Adam Quark Thursday, December 6, 2018 3:38 PM
    Thursday, December 6, 2018 6:27 AM

All replies

  • That is a little weird syntax, isn't it?

    DELETE a from Prod_table a

    join...

    ....

    Or you can with a select in a CTE and then delete from the cte.


    ;with mycte as (

    select * from Prod_table a   inner join Stage_table bon a.Date_Group = b.Date_Group and a.Date_of_Service = b.Date_of_Service and a.Claim_Number = b.Claim_Number and a.Claim_Line_Number = b.Claim_Line_Number and a.Mbr_ID = b.Mbr_ID)

    Delete from mycte;

    Another option is to use MERGE to delete stuff on some conditions.

    Wednesday, December 5, 2018 8:28 PM
    Moderator
  • Hi Adam,

    If you need to rewrite the statement above with WHERE clause, the delete statements could be written like this.'

    DELETE FROM Prod_table
    WHERE EXISTS (
                 SELECT 1 FROM Stage_table WHERE Date_Group=Prod_table.Date_Group
    			                                 AND Date_of_Service=Prod_table.Date_of_Service
    											 AND Claim_Number=Prod_table.Claim_Number
    											 AND Claim_Line_Number=Prod_table.Claim_Line_Number
    											 AND Mbr_ID=Prod_table.Mbr_ID
    
    )

    And the statement could be equal to:

    DELETE T
    FROM Prod_table T
    WHERE EXISTS (
                 SELECT 1 FROM Stage_table WHERE Date_Group=T.Date_Group
    			                                 AND Date_of_Service=T.Date_of_Service
    											 AND Claim_Number=T.Claim_Number
    											 AND Claim_Line_Number=T.Claim_Line_Number
    											 AND Mbr_ID=T.Mbr_ID
    
    )

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 6, 2018 3:22 AM
    Moderator
  • DELETE Prod_table
    from   Stage_table b
    where Prod_table.Date_Group = b.Date_Group
             and Prod_table.Date_of_Service = b.Date_of_Service
             and Prod_table.Claim_Number = b.Claim_Number
             and Prod_table.Claim_Line_Number = b.Claim_Line_Number
             and Prod_table.Mbr_ID = b.Mbr_ID
    Thursday, December 6, 2018 4:40 AM
  • The DELETE statement looks good to me

    the Prod_table after DELETE represents table to be deleted

    The FROM part includes logic based on which DELETion has to be happen which is where you've the join syntax between production and Staging based on those columns

    So what you've is a valid syntax. You can write it in other ways too

    like for example

    1. using aliases rather than repeating table names

    like

    DELETE a
    from  Prod_table a
       inner join Stage_table b
          on a.Date_Group = b.Date_Group
             and a.Date_of_Service = b.Date_of_Service
             and a.Claim_Number = b.Claim_Number
             and a.Claim_Line_Number = b.Claim_Line_Number
             and a.Mbr_ID = b.Mbr_ID

    2. Using EXISTS

    DELETE a
    from  Prod_table a
    WHERE EXISTS (
             SELECT 1
             FROM Stage_table b
             WHERE a.Date_Group = b.Date_Group
             and a.Date_of_Service = b.Date_of_Service
             and a.Claim_Number = b.Claim_Number
             and a.Claim_Line_Number = b.Claim_Line_Number
             and a.Mbr_ID = b.Mbr_ID
            )

    etc

    All of these are equivalent statements and works the same way


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Adam Quark Thursday, December 6, 2018 3:38 PM
    Thursday, December 6, 2018 6:27 AM
  • Adam, must read Steve's blog here 

    http://sqlblog.com/blogs/steve_kass/archive/2009/04/30/delete-from-where.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 6, 2018 6:43 AM
    Answerer
  • Thanx for the description, Visakh! That helps. It still looks a little wonky to me, but that's probably because I'm trying to mentally fit it into a SELECT statement sort of form. But, with the explanation, I understand better what's happening.

    Thanx again!

    Thursday, December 6, 2018 3:41 PM