Delete row with matching Company_ID

[blackburn2413]

Hey everyone, got an interesting one and just looking to be pointed in the right direction:

I have 2 tables in my database: "current clients" and "clients that canceled". Each company has a unique company ID that is used in both tables. What my goal is is to write something that will take all of the company ID's from clients that canceled and then search the "current clients" table and delete all companies with matching ID's.

I am coming from a world of mySQL and php, but unfortunately my company now requires Sharepoint and Office products to be used exclusively. I was figuring I could run a loop that takes each canceled company ID line by line and checks the other table to see if it exists. If it finds a result, then I would want it to delete that entire row of data.



That's the goal. Anyone have a direction they can point me in? Is this something best suited for a customized macro?

Thanks in advance!

[orange]

Be cautious of DELETE query. There's NO simple UNDO.

I recommend you Backup your database first.
Create a SELECT query to make sure you are dealing with the proper records.
Once you are sure you have the correct records.\, convert your SELECT query to a DELETE query.

Backup your database first.

There are example DELETE queries showing syntax at http://www.techonthenet.com/sql/delete.php

Don't forget to Backup your database.

[blackburn2413]

Thanks for that. I am running queries now that are working out and returning the results that I need, so step one complete I guess The next issue would be, how can I change the criteria that I am using for my query? For instance: Table2 we'll call it has a list of unique ID numbers in a field, I can manually setup a query with an ID number and delete any matching records from Table1. I can get that far at this point. What I am wondering is if there is a way to almost run it in a loop so that it takes every ID number stored in Table2 and deletes all matching records in Table1. So almost a dynamic query in a sense that the criteria can change based on the data stored in Table2.

Does that make sense?

[orange]

Step 1 is BACKUP your database.

Select Table1.* from Table1 Inner join Table2 on
Table1.id = Table2.ID

This type of query should give you the records in Table1 that have matching numbers(IDs) in table2.
Is that what you are doing?

[blackburn2413]

That was not the initial way I planned on doing it, but I have entered that as you mentioned and see that it is working. My apologies on behind the curve, I am so used to using PHP and having a loop counter and subbing in each value from the table with a variable like $row[$i] or something like that. Just a little bit of new Syntax work I guess.

So theoretically, I can just alter this select statement to make sure it is getting all the results I want and then change it over to a delete statement to get my requested results?

[orange]

Yes, but do the backup because there is no simple recovery if there is an error.
Be very careful with the delete query syntax...

I would look at something like this set up, but check the link I gave you before for syntax and BACKUP first.

DELETE FROM Table1 WHERE ID
IN ( select ID from Table1 inner join table2 on
Table1.ID = Table2.ID)

[blackburn2413]

Thank you, I really appreciate it- this data I am working with is easily exportable (from our isolated Reporting server) so I am just messing around with it for now. I actually just got this working which is great to see. I really appreciate the help today!

I am going to keep messing with some of the options and see what else I can have this do.

Thanks again!

[orange]

Good for you , glad to help.