DELETING RECORDS FROM TABLE WITH NESTED WHERE CLAU

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DELETING RECORDS FROM TABLE WITH NESTED WHERE CLAU

Author

Topic

rockingdesi
Starting Member

31 Posts

Posted - 2007-09-11 : 16:14:19

Hi,

I have a query that is executing properly but i want to delete the results of the query. I am trying to do it but i am messing up somewhere in the syntax

Can anybody help me out with this problem?
Below is the query
DELETE FROM DPT_NEW_BINS WHERE(
Select BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_NEW_BINS o
WHERE EXISTS (SELECT BIN,LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16), ISO_CTRY_CD, REGN_CD ,PROD_TYPE_CD FROM DPT_temp_NEW_BINS i
WHERE o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX))

CAN SOMEBODY POINT OUT MY MISTAKE?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-11 : 16:17:08

Your SELECT statement seems a bit complex for something that I think can be accomplished with a simple join:

DELETE o
FROM DPT_NEW_BINS o
INNER JOIN DPT_temp_NEW_BINS i
ON o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX

To make sure that it works before actually running it, run this:

SELECT *
FROM DPT_NEW_BINS o
INNER JOIN DPT_temp_NEW_BINS i
ON o.ACCT_NUM_MIN = i.ACCT_NUM_MIN AND o.ACCT_NUM_MAX = i.ACCT_NUM_MAX

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/