Delete ALL records where table join produces values

  • Hi,

    I need to delete ALL records from 'Column 0' in table 'dbo.abn_extract' if the following statement is true/returns a value.

    SELECT E.[Column 0] FROM dbo.abn_extract E JOIN dbo.ABN_File_Header H ON

    SUBSTRING(E.[Column 0], 1, 13) = H.Header_Id

    Any help please?

    Thanks in advance,

    Neal

  • Untested, of course... ๐Ÿ˜‰

    DELETE dbo.abn_extract

    FROM dbo.abn_extract E

    INNER JOIN dbo.ABN_File_Header H

    ON SUBSTRING(E.[Column 0], 1, 13) = H.Header_Id

    SQL Server is one of the few RDBMS's that have a FROM clause (2 actually) available... everyone else has to use correlated sub-queries. Lookup DELETE in Books Online for a better understanding of the statement.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff,

    Your solution deletes the 1 record in the dbo.abn_extract table that exists in the WHERE clause... However, I need to delete ALL records in the dbo.abn_extract table where there is a match.

  • How did you verify that? Is it just because you got a rows affected of 1? Perhaps there was only one row that met the condition?

    What do you get when you run your original query of...

    SELECT E.[Column 0] FROM dbo.abn_extract E JOIN dbo.ABN_File_Header H ON

    SUBSTRING(E.[Column 0], 1, 13) = H.Header_Id

    Sometimes, the devil is in the data... please read and heed the link in my signature line to get better help quicker. Thanks, Neal.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • My original query is fine...

    There is NO problem with the data.

    In both mine and your queries there is (currently) one record match.

    What I'm saying is that if there is a match (a value is returned) then delete ALL records from the dbo.abn_extract table.

    If SUBSTRING(E.[Column 0], 1, 13) = H.Header_Id is true on one dbo.abn_extract record then I need to delete ALL other [Column 0] records as well (even though only 1 [Column 0] record matches).

  • Hi Neal

    I think there's some confusion here because of this...

    delete ALL records from 'Column 0'

    and this

    delete ALL other [Column 0] records

    I reckon what you mean to say is this: if a matching row is found in dbo.ABN_File_Header, then delete all rows in dbo.abn_extract, otherwise don't delete any.

    The all-or-nothing nature of this requirement suggests the use of IF:

    IF (SELECT COUNT(*)

    FROM dbo.abn_extract E

    JOIN dbo.ABN_File_Header H

    ON SUBSTRING(E.[Column 0], 1, 13) = H.Header_Id) > 0

    BEGIN

    DELETE FROM dbo.abn_extract

    END

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks Chris,

    Thats excellent, Just the job!

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

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