Deleting Unmatched records

[Kelvin Leong]

Hi there,

I have 2 tables, namely tbl1 and tbl2, and they are linked by a common Key1.
Due so some reason the records in tbl1 was deleted but not linked records in
tbl2.

I have created an unmatching query, using the wizard, on tbl2 with
comparison to tbl1 to filter out which records in tbl1 no longer exist, while
linked records in tbl2 still do, via this Key1.

SQL View of query:
SELECT tbl2.* FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1 WHERE
(((tbl1.Key1) Is Null));

I then changed it to a DELETE query and the query became:

SQL View of query:
DELETE tbl2.* FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1 WHERE
(((tbl1.Key1) Is Null));

However, when I run this query, the system gave me this error message:
"Specify the table containing the records you want to delete."

The following is the Design View of the delete query:
Field | tbl2.* | tbl1.Key1 |
Table | tbl2 | tbl1 |
Delete | Where | Where |
Criteria | | Is Null |
or | | |

I wanted to change the "Where" for the tbl2 but it does not allow me to do
so. How can I delete all of the unmatched records in tbl2 as described above?

Best regards,

Kelvin

 

[arthurjr07]

try

DELETE*FROM tbl2
WHERE Key1 NOT IN
(SELECT Key1 FROM tbl1)

Be sure to back up
tbl2 before you do some
experiments.

 

[Chris2]

Hi there,

I have 2 tables, namely tbl1 and tbl2, and they are linked by a common Key1.
Due so some reason the records in tbl1 was deleted but not linked records in
tbl2.

I have created an unmatching query, using the wizard, on tbl2 with
comparison to tbl1 to filter out which records in tbl1 no longer exist, while
linked records in tbl2 still do, via this Key1.

SQL View of query:
SELECT tbl2.* FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1 WHERE
(((tbl1.Key1) Is Null));

I then changed it to a DELETE query and the query became:

SQL View of query:
DELETE tbl2.* FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1 WHERE
(((tbl1.Key1) Is Null));

However, when I run this query, the system gave me this error message:
"Specify the table containing the records you want to delete."

The following is the Design View of the delete query:
Field | tbl2.* | tbl1.Key1 |
Table | tbl2 | tbl1 |
Delete | Where | Where |
Criteria | | Is Null |
or | | |

I wanted to change the "Where" for the tbl2 but it does not allow me to do
so. How can I delete all of the unmatched records in tbl2 as described above?

Best regards,

Kelvin

Kelvin,

Sample Tables:

CREATE TABLE tbl1
(Key1 TEXT(1)
,CONSTRAINT pk_tbl1
PRIMARY KEY (Key1)
)

CREATE TABLE tbl2
(Key1 TEXT(1)
,CONSTRAINT pk_tbl2
PRIMARY KEY (Key1)
)


Sample Data:

tbl1
G
K
N
O
P
X

tbl2
A
G
K
N
O
P
S
X


Expectations:

Values A and S in tbl2 should be deleted because they are not in
tbl1.

DELETE T2.*
FROM tbl2 AS T2
WHERE NOT EXISTS
(SELECT T1.*
FROM tbl1 AS T1
WHERE T1.Key1 = T2.Key1)


Results:

tbl2
G
K
N
O
P
X

It appears to delete the correct rows.


Sincerely,

Chris O.

 

[John Spencer]

Usually, one simple change will solve the problem. Add DistinctRow to the
delete query when you have a join in the delete query. You can add it if
you only have one table in the From clause, but it is not necessary.

DELETE DistinctRow tbl2.*
FROM [tbl2] LEFT JOIN tbl1 ON tbl2.Key1 = tbl1.Key1
WHERE (((tbl1.Key1) Is Null));