SQL Delete matching fields with 2 tables - simple but evading me. 1

[m6]

Yup, it's eluding me for some reason.

2 tables, the first table (tblData) contains 2 fields, txtField1 and Data. The second table (tblMatch) just txtField1.

I want to delete all the records in the first that are matched in the second. txtField1 is the common between the two tables.

my query is:

DELETE tblData.* FROM tblMatch INNER JOIN tblData ON tblMatch.txtField1 = tblData.txtField1

Using Access I can select the data, but I just can't delete it. Why me???

Thanks to all willing to give hints,

m6

 

[ajdesalvo]

The join is why you cannot delete as you wish. Try this:

DELETE tblData.* FROM tblData WHERE tblData.txtField1 IN (Select tblMatch.txtField1 From tblMatch) Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[m6]

Much thanks, that was the rub, just too many other problems to concentrate!

m6

 

[scriverb]

ajdesalvo is correct in the query that he has given you. You frustration has preceeded you for many of us. What you are describing is a delete query(tblData) with an INNER JOIN to a secondary table(tblMatch). It seems simple but SQL doesn't provide for a simple way to do this.

The SQL provided by ajdesalvo is basically a Delete query from the tblData of the records where the field txtField1 are included in a secondary query SELECT. This design works in most instances where the number of records is relatively small. But, I just want to point out where it can get you in trouble. Because the secondary select is in the WHERE portion of the query it is performed over and over again on tblMatch for each row in tblData. For this reason you can see if the number of records in tblData becomes very large or tblMatch is a very large table then the query will surely bog down simply due to the burden of work that it must perform due to huge numbers of records being queried many times.

Just be aware of this if you get in a situation where the number of records is large and this kind of query becomes very slow. I have overcome this problem by creating an UPDATE query that flags the records to be deleted in tblData. An UPDATE query allows for an INNER JOIN of the two tables and where you can update a field in tblData with let's say all 9's. Then create a subsequent query to DELETE all records from tblData with all 9's in that particular field. It is a two stage process but it works quite well and is efficient.

I hope this will help you in the future. Bob Scriver

 

[m6]

scriverb and ajdesalvo,

Logically, since the data to be deleted is worthless, then changing field data to indicate "DELETE" is valid. Then the delete is within the single table and should be much faster.

Again, thanks for the help and understanding.

PS I may submit this for a FAQ.

Mike aka m6

 

[scriverb]

I am pleased that we could help you. Bob Scriver