Using a Delete Query without a Primary Key Field

[mrussell71]

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.

 

[mwolf00]

can't you just use:

DELETE FROM tblBASE
WHERE EXISTS
(select * from tblDELETE) -----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

 

[KenReay]

Hi

You are getting different results because you are using different joins, which in turn cause differing number sof rows to be included in the 'set'.

to experimet, try turning your delete queries into SELECT queries, and you will see that different numbers of rows are returned Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mrussell71]

Thanks Ken,
I did try running those queries as SELECT and they also gave me different results. They did not give me the same number of records as the DELETE query which I found odd.

A SELECT query for the first example above returns 16607 records while the DELETE returns 15604 records.
The second example returns 9514 records while the DELETE returns 9142.
The third example returns 9441 records while the DELETE returns 15159 records.

Why is there a difference between the number of records returned for the SELECT and DELETE queries? I can't figure out a DELETE query that returns the same amount of records as the tblDELETE. Maybe that table contains records that aren't in tblBASE. I don't know, as all of the data comes from a vendor and I don't have any control over it.

Thanks