Delete ALL records where table join produces values

Question

Post reply

Delete ALL records where table join produces values

DerbyNeal

Hall of Fame

Points: 3854
More actions
July 25, 2008 at 3:37 am

#210437

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

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

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

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)

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

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)

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 6

DerbyNeal

Hall of Fame

Points: 3854

July 25, 2008 at 5:59 am

#848322

Thanks Chris,

Thats excellent, Just the job!