delete query

[me]

I get the message "cannot delete from selected tables", when I change a
select query into a delete query.

Suppose I have 2 simple tables with identical variables.

When I receive 1 or more items to update or delete in Table2, I can select
the corresponding records in Table1 on ItemID and two dates succesfully
with:

SELECT Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

However, I can not delete the selected records from Table1 by changing the
query into a delete query (gives error 3086):

DELETE Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

Will someone be so kind to help me solving this?

 

[Douglas J Steele]

Assuming lngItemId is the primary key for Table1, try:

DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2))

 

[me]

Table1 has no primary key, nor has Table2.

Your suggestion, on my system with for this example only these two tables in
a empty mdb, leads to deleting all records in Table1 with matching ItemID in
Table2, instead of only those records satisfying 2 matching dates as well.

(When I construct primary keys in both tables, based on ItemID, Date1,
Date2, and a 4th variable, I do not solve the problem though, eg deleting
wrong records.)

When executed, the syntax is slightly altered by Access to:
DELETE *
FROM Table1
WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN [Table2]
ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
[Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));

What is still wrong?

Assuming lngItemId is the primary key for Table1, try:

DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I get the message "cannot delete from selected tables", when I change a
select query into a delete query.

Suppose I have 2 simple tables with identical variables.

When I receive 1 or more items to update or delete in Table2, I can select
the corresponding records in Table1 on ItemID and two dates succesfully
with:

SELECT Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

However, I can not delete the selected records from Table1 by changing the
query into a delete query (gives error 3086):

DELETE Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

Will someone be so kind to help me solving this?

 

[Douglas J Steele]

The original query you posted was joining the tables on Table1.lngItemID =
[Table2].lngItemID

Your new query is joining them on Table1.lngItemID = [Table2].lngID

Which is correct?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Table1 has no primary key, nor has Table2.

Your suggestion, on my system with for this example only these two tables in
a empty mdb, leads to deleting all records in Table1 with matching ItemID in
Table2, instead of only those records satisfying 2 matching dates as well.

(When I construct primary keys in both tables, based on ItemID, Date1,
Date2, and a 4th variable, I do not solve the problem though, eg deleting
wrong records.)

When executed, the syntax is slightly altered by Access to:
DELETE *
FROM Table1
WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN [Table2]
ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
[Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));

What is still wrong?

Assuming lngItemId is the primary key for Table1, try:

DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I get the message "cannot delete from selected tables", when I change a
select query into a delete query.

Suppose I have 2 simple tables with identical variables.

When I receive 1 or more items to update or delete in Table2, I can select
the corresponding records in Table1 on ItemID and two dates succesfully
with:

SELECT Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

However, I can not delete the selected records from Table1 by changing the
query into a delete query (gives error 3086):

DELETE Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

Will someone be so kind to help me solving this?

 

[Guest]

I'm following this thread and wonder whether or not there is a glitch in
Access regarding the delete query. I had a similar problem and followed the
instructions in help at:
http://office.microsoft.com/en-us/assistance/HA010345581033.aspx for deleting
duplicat records. I got the same error message as ME.

So I build a new database with one table and five records of which only one
was a duplicate in four of five fields--the fifth being a numeric tie-breaker
to meet the requirements of the instructions mentioned above. After building
the two queries, I still get the same error message.

It would appear that one must build the queries using SQL in order for it to
work--and even then it apparently does not.

Why won't the delete query work when the "WHERE" criteria comes from another
table or query--there are no permission or read-only issues.
--
DMF

The original query you posted was joining the tables on Table1.lngItemID =
[Table2].lngItemID

Your new query is joining them on Table1.lngItemID = [Table2].lngID

Which is correct?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Table1 has no primary key, nor has Table2.

Your suggestion, on my system with for this example only these two tables in
a empty mdb, leads to deleting all records in Table1 with matching ItemID in
Table2, instead of only those records satisfying 2 matching dates as well.

(When I construct primary keys in both tables, based on ItemID, Date1,
Date2, and a 4th variable, I do not solve the problem though, eg deleting
wrong records.)

When executed, the syntax is slightly altered by Access to:
DELETE *
FROM Table1
WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN [Table2]
ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
[Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));

What is still wrong?

Assuming lngItemId is the primary key for Table1, try:

DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I get the message "cannot delete from selected tables", when I change a
select query into a delete query.

Suppose I have 2 simple tables with identical variables.

When I receive 1 or more items to update or delete in Table2, I can select
the corresponding records in Table1 on ItemID and two dates succesfully
with:

SELECT Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

However, I can not delete the selected records from Table1 by changing the
query into a delete query (gives error 3086):

DELETE Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

Will someone be so kind to help me solving this?

 

[me]

Sorry for the confusion. Actually, both are correct. In my effort to solve
the problem, I created sets of two drastically simplified tables in a new
database. And I am changing and trying variables, and names, and keys, and
indexes. Because I do not understand the error, as I have several perfectly
working deletequeries in my projects.

The original query you posted was joining the tables on Table1.lngItemID =
[Table2].lngItemID

Your new query is joining them on Table1.lngItemID = [Table2].lngID

Which is correct?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Table1 has no primary key, nor has Table2.

Your suggestion, on my system with for this example only these two

tables

in
a empty mdb, leads to deleting all records in Table1 with matching

ItemID

in
Table2, instead of only those records satisfying 2 matching dates as well.

(When I construct primary keys in both tables, based on ItemID, Date1,
Date2, and a 4th variable, I do not solve the problem though, eg deleting
wrong records.)

When executed, the syntax is slightly altered by Access to:
DELETE *
FROM Table1
WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN [Table2]
ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
[Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));

What is still wrong?


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht

Assuming lngItemId is the primary key for Table1, try:

DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I get the message "cannot delete from selected tables", when I

change

a

select query into a delete query.

Suppose I have 2 simple tables with identical variables.

When I receive 1 or more items to update or delete in Table2, I can select
the corresponding records in Table1 on ItemID and two dates succesfully
with:

SELECT Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

However, I can not delete the selected records from Table1 by

changing
the

query into a delete query (gives error 3086):

DELETE Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

Will someone be so kind to help me solving this?

 

[me]

May be I found the answer. After adding DISTINCTROW to the sql-syntax, the
query in the test database ran fine with muliple runs. The first tests on
the more complex 'real' tables ran fine as well.

It was just a guess, changing to Unique Records in queryproperties. After
all, the SELECT query gave right results in all cases.

I'm following this thread and wonder whether or not there is a glitch in
Access regarding the delete query. I had a similar problem and followed the
instructions in help at:
http://office.microsoft.com/en-us/assistance/HA010345581033.aspx for deleting
duplicat records. I got the same error message as ME.

So I build a new database with one table and five records of which only one
was a duplicate in four of five fields--the fifth being a numeric tie-breaker
to meet the requirements of the instructions mentioned above. After building
the two queries, I still get the same error message.

It would appear that one must build the queries using SQL in order for it to
work--and even then it apparently does not.

Why won't the delete query work when the "WHERE" criteria comes from another
table or query--there are no permission or read-only issues.
--
DMF

The original query you posted was joining the tables on Table1.lngItemID =
[Table2].lngItemID

Your new query is joining them on Table1.lngItemID = [Table2].lngID

Which is correct?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Table1 has no primary key, nor has Table2.

Your suggestion, on my system with for this example only these two

tables
in

a empty mdb, leads to deleting all records in Table1 with matching

ItemID
in

Table2, instead of only those records satisfying 2 matching dates as well.

(When I construct primary keys in both tables, based on ItemID, Date1,
Date2, and a 4th variable, I do not solve the problem though, eg deleting
wrong records.)

When executed, the syntax is slightly altered by Access to:
DELETE *
FROM Table1
WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN [Table2]
ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
[Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));

What is still wrong?


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
Assuming lngItemId is the primary key for Table1, try:

DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I get the message "cannot delete from selected tables", when I

change
a

select query into a delete query.

Suppose I have 2 simple tables with identical variables.

When I receive 1 or more items to update or delete in Table2, I can
select
the corresponding records in Table1 on ItemID and two dates succesfully
with:

SELECT Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
[Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

However, I can not delete the selected records from Table1 by changing
the
query into a delete query (gives error 3086):

DELETE Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
[Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

Will someone be so kind to help me solving this?

 

[Guest]

Actually I found a solution after poking around in the Queries Board. For
some reason queries default to "no" in the unique record property. By
changing the uinque record property in the query's properties list allows the
query to be run without the annoying error message. It's a lot easier than
messing with SQL language.
--
DMF

Sorry for the confusion. Actually, both are correct. In my effort to solve
the problem, I created sets of two drastically simplified tables in a new
database. And I am changing and trying variables, and names, and keys, and
indexes. Because I do not understand the error, as I have several perfectly
working deletequeries in my projects.

The original query you posted was joining the tables on Table1.lngItemID =
[Table2].lngItemID

Your new query is joining them on Table1.lngItemID = [Table2].lngID

Which is correct?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Table1 has no primary key, nor has Table2.

Your suggestion, on my system with for this example only these two

tables

in
a empty mdb, leads to deleting all records in Table1 with matching

ItemID

in
Table2, instead of only those records satisfying 2 matching dates as well.

(When I construct primary keys in both tables, based on ItemID, Date1,
Date2, and a 4th variable, I do not solve the problem though, eg deleting
wrong records.)

When executed, the syntax is slightly altered by Access to:
DELETE *
FROM Table1
WHERE lngItemID IN (SELECT Table1.lngItemID FROM Table1 INNER JOIN [Table2]
ON (Table1.lngItemID = [Table2].lngID) AND (Table1.dtDate1 =
[Table2].dtDate1) AND (Table1.dtDate2 = [Table2].dtDate2));

What is still wrong?


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
Assuming lngItemId is the primary key for Table1, try:

DELETE FROM Table1 WHERE lngItemID IN (SELECT Table1.lngItemId
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID = [Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I get the message "cannot delete from selected tables", when I

change

a
select query into a delete query.

Suppose I have 2 simple tables with identical variables.

When I receive 1 or more items to update or delete in Table2, I can
select
the corresponding records in Table1 on ItemID and two dates succesfully
with:

SELECT Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
[Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

However, I can not delete the selected records from Table1 by changing
the
query into a delete query (gives error 3086):

DELETE Table1.*
FROM Table1 INNER JOIN [Table2] ON (Table1.lngItemID =
[Table2].lngItemID)
AND (Table1.dtDate1 = [Table2].dtDate1) AND (Table1.dtDate2 =
[Table2].dtDate2);

Will someone be so kind to help me solving this?