Newbie delete question

[RikForgo]

I have two tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table?


TID_ADMIN
--------------------
ID | Service
--------------------
1 Army
2 Navy
3 Air Force


TIDS
--------------------------------------
ID | ADMIN_ID | Project Name
--------------------------------------
1 1 Project X
2 1 Project Y
3 2 Vision C
4 3 Clearout T
5 3 Clearout F
6 3 Trustee 433

 

[hvass]

Think I know what you mean but have not found a simple way to do it other than

creating temporary tables....

drop table if exists newtids;
create table newtids
select a.service,b.* from tid_admin a inner join tids b using(admin_id);
delete from newtids where service='AIRFORCE';
alter table newtids drop service;
select * from newtids;

or using perl routine to loop through creating a series of delete statements
delete from tids where id=4;
delete from tids where id=5;
delete from tids where id=6;

If you find something more elegant then would love to know..

 

[RikForgo]

Thanks for the post. I will give it a try. Perhaps a more elegant solution exists in version 4.X?

 

[GiffordS]

I'm not sure I understand the question. Are you assuming you don't know the Admin_ID of Air Force? If so, just two simple queries, really. One to extract the Admin_ID of Air Force, the other then to delete all records in TIDS where the Admin_ID = the result of the previous query, in this case, 3.

 

[RikForgo]

No, I'm sorry I wasn't clear. The TID_ADMIN table contains contact info on people who have made submissions to our program; each submitter is either Army, Navy or Air Force. So the ID in the TID_ADMIN table identifies the person, but not the service.