Delete records from table 1 which match records from table 2

  • Thread starter Thread starter Matilda
  • Start date Start date
M

Matilda

G'day Gurus,

I have a table (T1) which holds records which are a subset of table 2 (T2).
I want to delete the records from T2 which match those in T1.

I have tried a Find Unmatched query which I would then make a new table
from, but I get zero records returned from this tactic - can't fathom why
that would be the case ):-((

Also tried an SQL approach given here for a similar problem, but get error
messages.

I'm sure it's simple but so far eludes me.

Many TIAs - you've never let me down

Matilda
 
Matilda said:
I have a table (T1) which holds records which are a subset of table 2 (T2).
I want to delete the records from T2 which match those in T1.

I have tried a Find Unmatched query which I would then make a new table
from, but I get zero records returned from this tactic - can't fathom why
that would be the case ):-((
Click to expand...

You didn't mention how to match between the 2 tables. In this example,
let's assume they both have the same unique field "ItemID". Then you
can delete T2 records whose ItemID value is found in a subquery based on T1.

(Do this on a *copy* of your database first in case I flubbed the syntax.)

DELETE FROM T2
WHERE T2.ItemID IN
(SELECT T1.ItemID FROM T1)

Good luck,
Hans
 
Thanks Hans.

That is a similar solution to the one I tried, and I still get an error msg.
With this statement it gives the msg
"Query must have at least one destination field".
The only alteration to your code that I made was to substitute my table and
field names.
Does that make any sense to you?

Matilda
 
Matilda said:
Thanks Hans.

That is a similar solution to the one I tried, and I still get an error msg.
With this statement it gives the msg
"Query must have at least one destination field".
The only alteration to your code that I made was to substitute my table and
field names.
Does that make any sense to you?
Click to expand...

I don't see what the problem is. I just now actually created the tables
and query (in Access 2003). This is what SQL View of my query contains:

DELETE
FROM T2
WHERE (((T2.ItemID) In (SELECT T1.ItemID FROM T1)));

The query ran without complaint and removed the appropriate records from
T2.

So, I don't know what to suggest next. Perhaps, try adding a space and
a * character after DELETE to see if that will satisfy Access' demand
for a destination field. I seem to recall Access adding a * when
designing DELETE queries in the query editor, but I never thought it was
a *requirement*.

If that still doesn't fix you up, show us the SQL View of the actual
query that isn't working for you. Maybe one of us can spot the problem.

Good luck,
Hans
 
Thanks Hans, that did it!!

Matilda

Hans Up said:
I don't see what the problem is. I just now actually created the tables
and query (in Access 2003). This is what SQL View of my query contains:

DELETE
FROM T2
WHERE (((T2.ItemID) In (SELECT T1.ItemID FROM T1)));

The query ran without complaint and removed the appropriate records from
T2.

So, I don't know what to suggest next. Perhaps, try adding a space and
a * character after DELETE to see if that will satisfy Access' demand
for a destination field. I seem to recall Access adding a * when
designing DELETE queries in the query editor, but I never thought it was
a *requirement*.

If that still doesn't fix you up, show us the SQL View of the actual
query that isn't working for you. Maybe one of us can spot the problem.

Good luck,
Hans
Click to expand...
 
Hans:

I think the problem was that Matilda was trying to preview the rows to be
deleted as a datasheet. A DELETE statement will execute happily without any
columns being specified, but if you try an switch to datasheet view before
executing the query the error which Matilda experienced will be raised.
Adding the asterisk enables it to be viewed as a datasheet before executing
the statement.

Its generally more efficient to use the EXISTS predicate in cases like this:

DELETE *
FROM T2
WHERE EXISTS
(SELECT * FROM T1
WHERE T1.ItemID = T2.ItemID);

Both will work when looking for matches, but when looking for non-matches
the latter is to be preferred because of the potential problem with Nulls.
NOT IN(SELECT ItemID FROM T1) will not evaluate to True if there is a Null at
the ItemID column position in any row in T1. This is because it’s the
equivalent of a series of Boolean AND operations so if a row in T2 has a
value 'y' in the ItemID column and T1 has values 'x', 'z' and a Null in its
rows then the expression equates to 'y'<>'x' AND 'y'<>'z' AND 'y' <> NULL.
Now because comparison with Null always results in a Null, not a True or
False the whole expression will not evaluate to True as an AND operation
requires all to evaluate to True. When looking for matches Nulls don't
matter as, if we assume that T1 also contains a 'y', it equates to 'y'='x' OR
'y' = 'y' OR 'y' = 'z' OR 'y' = NULL. As the second of these evaluates to
True the fact that the last evaluates to Null is irrelevant as in an OR
operation only one has to evaluate to True. Mathematically this can be
demonstrated more conclusively by applying DeMorgan's Laws, but we don't need
to go that far here.

So if one wished to delete non-matches rather than matches the following
would be best:

DELETE *
FROM T2
WHERE NOT EXISTS
(SELECT * FROM T1
WHERE T1.ItemID = T2.ItemID);

Ken Sheridan
Stafford, England
 
Ken said:
Hans:

I think the problem was that Matilda was trying to preview the rows to be
deleted as a datasheet. A DELETE statement will execute happily without any
columns being specified, but if you try an switch to datasheet view before
executing the query the error which Matilda experienced will be raised.
Adding the asterisk enables it to be viewed as a datasheet before executing
the statement.
Click to expand...

Aha! Thanks so much, Ken. I was unreasonably agitated by the thought
that Access would ever require a * after DELETE. Now I see how it would
be useful to allow the user to preview which records are subject to
deletion.
Its generally more efficient to use the EXISTS predicate in cases like this:
Click to expand...

Thanks again, Ken. My habit has been to reach for IN, and overlook
EXISTS. I'll read your discussion enough times to burn EXISTS into my
brain. :-)

Regards,
Hans
 
Back
Top