Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete ALL records where table join produces values Expand / Collapse
Author
Message
Posted Friday, July 25, 2008 3:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 150, Visits: 821
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
Post #540766
Posted Friday, July 25, 2008 4:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #540774
Posted Friday, July 25, 2008 4:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 150, Visits: 821
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.
Post #540778
Posted Friday, July 25, 2008 4:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #540799
Posted Friday, July 25, 2008 5:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 150, Visits: 821
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).
Post #540808
Posted Friday, July 25, 2008 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 6,800, Visits: 14,015
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
Exploring Recursive CTEs by Example Dwain Camps
Post #540815
Posted Friday, July 25, 2008 5:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 150, Visits: 821
Thanks Chris,

Thats excellent, Just the job!

Post #540834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse