Re: Deleting Unmatched records


"Kelvin Leong" <KelvinLeong@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:305FE7C6-EAE5-42B8-A59C-1CED8FB52829@xxxxxxxxxxxxxxxx
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.


.

Relevant Pages

  • RE: Join two tables with 3 fields; 1 field doesnt match exactly
    ... Another string evaluation would be the Instrfunction, ... trying to join these fields in order to run a query that brings together data ... In Tbl2, ... There could be several engines listed in the options field in Tbl1 but only ...
    (microsoft.public.access.modulesdaovba)
  • RE: Join two tables with 3 fields; 1 field doesnt match exactly
    ... trying to join these fields in order to run a query that brings together data ... In Tbl2, ... There could be several engines listed in the options field in Tbl1 but only ... The Mid function above would select only the first two characters of Tbl1 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Combine Tables
    ... Tbl1 is single field: ... Tbl2 has 2 fields: ... that determines where, the dash is. ... THEN use the query and tbl2 to create another query that joins ...
    (comp.databases.ms-access)
  • Re: sequence for tickets
    ... Create a table of number (tblNums) with a single, ... FROM tbl1, tblNums ... You can convert this to a make table or append query to get your records ... my output table is tbl2 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Foreign Key problem
    ... If you want to create a record in tbl2 as soon as a new record is entered in tbl1, use the AfterInsert event procedure of the *form* where the data is added. ... updated with a primary key and matching foreign key on insert. ...
    (microsoft.public.access.formscoding)