Solved

How To Delete Mutiple Records Found Be

Posted on 2016-08-04
6
29 Views
Last Modified: 2016-10-08
I will try to make this as clear as possible.  My main objective is to "Identify" and then "Delete" records found between two tables.  I've solved the first part of "Identifying" the records that needs to be deleted, however, I'm a little stuck on how to delete the 370K records found.  The below query is what I use to locate the records:  It works fine.

SELECT TABLE1.COLUMN1 AS ACCOUNTS, TABLE2.COLUMN1 AS COMPANY_HQ
FROM TABLE1, TABLE2
WHERE TABLE1.ACCOUNT + '%%%%%%' LIKE '%%%%%%' + TABLE2.COMPANY_HQ + '%%%%%%'
ORDER BY TABLE2.COMPANY_HQ ASC;

(see attached file for results)

Now...I'm using the below query to delete the records found in Table2:

DELETE FROM TABLE2
WHERE TABLE2.ACCOUNT + '%%%%%%' LIKE '%%%%%%' + TABLE1.COMPANY_HQ + '%%%%%%'

BUT...I get the below error message:

Msg 4104, Level 16, State1, Line 2
The multi-part identifier "TABLE2.COMPANY_HQ" could not be bound.

I apologize for any confusion.  Any assistance in this matter would be greatly appreciated.
SQL-Result.PNG
0
Comment
Question by:Member_2_7970395
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 41742826
Both queries you posted are missing the JOIN ... ON criteria that tells SQL Server how the two tables are related.

<knee-jerk answer> You're missing the JOIN in your DELETE Table, so assming just id..
DELETE Table2
FROM TABLE2 t2
  JOIN Table1 t1 ON t2.id = t2.id
WHERE TABLE2.ACCOUNT + '%%%%%%' LIKE '%%%%%%' + TABLE1.COMPANY_HQ + '%%%%%%'

Open in new window

For DELETE with JOIN formatting help check out this Pinal Dave blog.
1
 
LVL 20

Accepted Solution

by:
Russ Suter earned 250 total points
ID: 41742977
The clearest way (I think) to do this is to use a subquery. I don't mess around with DELETE statements. I want them to be as clear as possible. What I like about this approach is that you can run the SELECT statement independently to verify that what you're about to delete is correct. The syntax looks something like this:
DELETE FROM
  Table2
WHERE
  Table2.Table1ForeignKey IN
  (
  SELECT
    Table1.PrimaryKey
  FROM
    Table1
  WHERE
    SomeCondition = True
  )

Open in new window

1
 

Author Comment

by:Member_2_7970395
ID: 41743067
It worked!  Thank you very much!!
0
 

Author Comment

by:Member_2_7970395
ID: 41743098
Don't if I'm entering this correctly, but if not, I want to THANK all of you who responded.
0
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41743124
Glad we could help.

Looks like you're trying to close this question. I think they've made that more confusing. You can either click "Close Question" at the bottom of your browser or click "Best Answer" in the comment you want to accept. You can then edit to accept multiple solutions.

Happy SQLing!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question