Delete Query returning too many records

[unique12u]

Delete Query based on another table

I am trying to run the following Delete Query

DELETE Master.*
FROM Master
WHERE((Master.glm_account) In (SELECT glj.glm_account from glj) AND (Master.glm_prft_ctr) In (SELECT glj.glm_prft_ctr from glj));


The problem is it is trying to delete all the records with the account numbers = and ALL the records with profit center equal. I am getting 2564 records instead on 147 records.

The two tables are related by glm_account AND glm_prft_ctr - BOTH must be equal in order to delete

If I do an INNER Join or add the other table to the Query it gives me "Cannot Delete Records from Specified Table"

Help

TIA
Nique

 

[Golom]

Presumably you want "glm_account" and "glm_prft_ctr" to be equal on the SAME record. Your SQL will produce a match as long as each field exists in SOME record though not necessarily the same one. Try something like

DELETE Master.*

FROM Master

WHERE cStr(glm_account) & cStr(glm_prft_ctr) IN 
     (SELECT cStr(glm_account) & cStr(glm_prft_ctr) FROM glj)