Delete query balking 1

[TomYC]

Thanks to other threads I've searched, from which I learned about Left outer joins, I have a query that identifies records to be deleted: those with default "Unknown" values in two fields in my "main" table, and no values (null) in a linked "issues" table.

Here is my sql:

SELECT t_wr_main.*, t_issue.issue_id
FROM t_wr_main LEFT JOIN t_issue ON t_wr_main.id = t_issue.id
WHERE (((t_wr_main.team)="Unknown") AND ((t_wr_main.unit)="Unknown") AND ((t_issue.issue_id) Is Null));

When I try and turn this into a delete query, however, to delete those records from t_wr_main that fulfill the criteria, I get the following message instead:

"could not delete from specified tables."

Here is the delete query sql:

DELETE t_wr_main.*, t_wr_main.team, t_wr_main.unit, t_issue.issue_id
FROM t_wr_main LEFT JOIN t_issue ON t_wr_main.id = t_issue.id
WHERE (((t_wr_main.team)="Unknown") AND ((t_wr_main.unit)="Unknown") AND ((t_issue.issue_id) Is Null));

What do I have wrong here, or is there an FAQ on deletes?

 

[lespaul]

one way is to use a NOT IN:

DELETE FROM t_wr_main where t_wr_main.team ="Unknown" AND t_wr_main.unit ="Unknown" and t_wr_main.id not in (select issue_id From t_issue)

typed not tested...always make a backup before running a delete query!!

Leslie

Come join me at New Mexico Linux Fest!

 

[TomYC]

thanks, lespaul (how's your elbow?)--
No error message, but no delete either!
and when I change your query to a select query, it doesn't find my records of interest, so I'm not sure how to correct this--in my table I can see two records to identify and delete, but I want to be able to do this with a button, or even automatically, once I have perfected this process.

 

[PHV]

DELETE * FROM t_wr_main WHERE id NOT IN (SELECT id FROM t_issue WHERE id Is Not Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TomYC]

That did it, PHV--
And in one line, at that!
I would like to understand the parenthesis expression, however...
Tom