Deleting Outer Join Records

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

Guest

Ok, I have a database that contains a table with current construction
projects. It will soon contain a table with the updated constructions
projects. I have a Left Outer Join that returns the records that are in the
Original_Table but not in the Updated_Table. Using the following concept:

SELECT *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

Is there a way to get Access to delete these records? I changed it into a
delete query and I keep getting the following message:

"Specify the table containing the records that you want to delete."

I am assuming that this message appears because it adds the
[Updated_Table].ID field to the query. Does anyone have any suggestions?

Thanks in advance,
Suzie
 
Hi,


Add DISTINCTROW from the delete query:


DELETE DISTINCTROW tableNameToDeleteFrom.* FROM table1 SOME JOIN table2
.....


Note also that you have to specify FROM WHICH table you delete, since the
JOIN implies many tables, and you probably wisely wish to delete from just
one of them.



Hoping it may help,
Vanderghast, Access MVP
 
The DISTINCTROW doesn't help me much. How do I specify which table the
records should be deleted from? When I change the query to a delete query my
SQL looks like the following (this was created by Access when I changed the
type of query):

DELETE DISTINCTROW *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

This is when I get the error message.

Any other help is appreciated!

Michel Walsh said:
Hi,


Add DISTINCTROW from the delete query:


DELETE DISTINCTROW tableNameToDeleteFrom.* FROM table1 SOME JOIN table2
.....


Note also that you have to specify FROM WHICH table you delete, since the
JOIN implies many tables, and you probably wisely wish to delete from just
one of them.



Hoping it may help,
Vanderghast, Access MVP


Suzie Raboin said:
Ok, I have a database that contains a table with current construction
projects. It will soon contain a table with the updated constructions
projects. I have a Left Outer Join that returns the records that are in
the
Original_Table but not in the Updated_Table. Using the following concept:

SELECT *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

Is there a way to get Access to delete these records? I changed it into a
delete query and I keep getting the following message:

"Specify the table containing the records that you want to delete."

I am assuming that this message appears because it adds the
[Updated_Table].ID field to the query. Does anyone have any suggestions?

Thanks in advance,
Suzie
Click to expand...
Click to expand...
 
Hi,


I would suspect you delete from the table Original_Table:


DELETE DISTINCTROW Original_Table.* FROM Original_Table LEFT JOIN ...


Vanderghast, Access MVP
 
Well, I've taken care of the problem. I'm still not really sure what was
wrong with it. I just started over and now it works fine. Thanks for your
help!

Suzie Raboin said:
The DISTINCTROW doesn't help me much. How do I specify which table the
records should be deleted from? When I change the query to a delete query my
SQL looks like the following (this was created by Access when I changed the
type of query):

DELETE DISTINCTROW *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

This is when I get the error message.

Any other help is appreciated!

Michel Walsh said:
Hi,


Add DISTINCTROW from the delete query:


DELETE DISTINCTROW tableNameToDeleteFrom.* FROM table1 SOME JOIN table2
.....


Note also that you have to specify FROM WHICH table you delete, since the
JOIN implies many tables, and you probably wisely wish to delete from just
one of them.



Hoping it may help,
Vanderghast, Access MVP


Suzie Raboin said:
Ok, I have a database that contains a table with current construction
projects. It will soon contain a table with the updated constructions
projects. I have a Left Outer Join that returns the records that are in
the
Original_Table but not in the Updated_Table. Using the following concept:

SELECT *
FROM Original_Table LEFT JOIN [Updated_Table] ON Original_Table.ID =
[Updated_Table].ID
WHERE [Updated_Table].ID IS NULL;

Is there a way to get Access to delete these records? I changed it into a
delete query and I keep getting the following message:

"Specify the table containing the records that you want to delete."

I am assuming that this message appears because it adds the
[Updated_Table].ID field to the query. Does anyone have any suggestions?

Thanks in advance,
Suzie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top