mrussell71
IS-IT--Management
I am receiving unexpected results from a Delete Query that uses two nonrelated tables. The first table is the base table that I want to remove the records from and the second is a list of all of the records that should be removed. The fields in each table are:
appID, 7 digit number
catID, 4 digit number
partID, alphanumeric
descA, text field
descB, text field
The base table contains 523155 records, while the delete table contains 16498 records.
When I create a delete query, linking the appID and partID fields, the query returns 15604 records to be deleted. If I create a delete query and link the appID, catID, and partID fields the query returns 9142 records to be deleted. AND if i create a query using appID, partID, and descA, the delete query returns 15159 records.
The sql that I am using is as follows:
[tt]DELETE tblBASE.*
FROM tblBASE
WHERE EXISTS
(select * from tblDELETE
where tblBASE.appID = tblDELETE.appID
and tblBASE.partID = tblDELETE.partID);
DELETE tblBASE.*
FROM tblBASE
WHERE EXISTS
(select * from tblDELETE
where tblBASE.appID = tblDELETE.appID
and tblBASE.catID = tblDELETE.catID
and tblBASE.partID = tblDELETE.partID);
DELETE tblBASE.*
FROM tblBASE
WHERE EXISTS
(select * from tblDELETE
where tblBASE.appID = tblDELETE.appID
and tblBASE.partID = tblDELETE.partID
and tblBASE.descA = tblDELETE.descA);[/tt]
Unfortunately, I cannot create a primary key in either of the tables, as it would create duplicate values. Well that is the error I receive when I try. Can't figure out why I am getting different results from the different queries.
Any help would be appreciated.
appID, 7 digit number
catID, 4 digit number
partID, alphanumeric
descA, text field
descB, text field
The base table contains 523155 records, while the delete table contains 16498 records.
When I create a delete query, linking the appID and partID fields, the query returns 15604 records to be deleted. If I create a delete query and link the appID, catID, and partID fields the query returns 9142 records to be deleted. AND if i create a query using appID, partID, and descA, the delete query returns 15159 records.
The sql that I am using is as follows:
[tt]DELETE tblBASE.*
FROM tblBASE
WHERE EXISTS
(select * from tblDELETE
where tblBASE.appID = tblDELETE.appID
and tblBASE.partID = tblDELETE.partID);
DELETE tblBASE.*
FROM tblBASE
WHERE EXISTS
(select * from tblDELETE
where tblBASE.appID = tblDELETE.appID
and tblBASE.catID = tblDELETE.catID
and tblBASE.partID = tblDELETE.partID);
DELETE tblBASE.*
FROM tblBASE
WHERE EXISTS
(select * from tblDELETE
where tblBASE.appID = tblDELETE.appID
and tblBASE.partID = tblDELETE.partID
and tblBASE.descA = tblDELETE.descA);[/tt]
Unfortunately, I cannot create a primary key in either of the tables, as it would create duplicate values. Well that is the error I receive when I try. Can't figure out why I am getting different results from the different queries.
Any help would be appreciated.