Delete Based On Another Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to set up a delete query and could use some assistance. I have a table with several fields. I have another table with three fields and about 14 records. The first table has three of the fields that match these fields. What I need to do is set up a query that will delete any record in the first table based on the values in three of the fields of the second talbe. Basically it needs to go through each record in the second table, and with those three values, delete any record in the first table. I set up a query that will delete the records but I have to put the exact value in the expression. How do I set the expression to be equal to the second table and do it for each record

I would really appreciate any help on this topic that you could give me.
 
Do you mean if a Record in Table1 has 3 values that
exactly match 3 corresponding values in a Record in Table2
then delete the Record in Table1?

If that's the case, try:

****Untested****
DELETE *
FROM T1
WHERE EXISTS
(
SELECT *
FROM T2
WHERE (T2.FieldA = T1.FieldA)
AND (T2.FieldB = T1.FieldB)
AND (T2.FieldC = T1.FieldC)
)
****

There may be other (more efficient) SQL Strings.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
I am trying to set up a delete query and could use some
Click to expand...
assistance. I have a table with several fields. I have
another table with three fields and about 14 records. The
first table has three of the fields that match these
fields. What I need to do is set up a query that will
delete any record in the first table based on the values
in three of the fields of the second talbe. Basically it
needs to go through each record in the second table, and
with those three values, delete any record in the first
table. I set up a query that will delete the records but
I have to put the exact value in the expression. How do I
set the expression to be equal to the second table and do
it for each record.
 
Back
Top