delete query will not work with tables joined in the query

[MorningStarFan]

Hello,

We have two tables in a query and joined on ID for a one time data project
and we need to delete some records from one of the tables. We are using the
other table to identify our records to be deleted. Our delete query won't
allow records to be deleted. We get the error message "Could not delete from
specified tables (Error 3086)". ANy ideas? I can manually delete the records
one by one...

 

[Ken Snell [MVP]]

That error occurs when Jet cannot identify the unique record that is to be
deleted. Post the SQL statement that produces this error, along with an
explanation of which table's records are to be deleted, and we can assist
you with rewriting it to get around this problem.

 

[MorningStarFan]

Okay, thank you. The table NEW_ID_TABLE is the one we want to delete records
from based on the criteria in XALUMNIARTLIST.

DELETE NEW_ID_TABLE.*, NEW_ID_TABLE.FULL_NAME,
XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
FROM NEW_ID_TABLE INNER JOIN XALUMNIARTLIST ON NEW_ID_TABLE.[@ID] =
XALUMNIARTLIST.[@ID]
WHERE (((XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR)<=55));

 

[Ken Snell \(MVP\)]

One of these queries should do the trick for you:


DELETE NEW_ID_TABLE.*
FROM NEW_ID_TABLE
INNER JOIN XALUMNIARTLIST
ON NEW_ID_TABLE.[@ID] =
XALUMNIARTLIST.[@ID]
WHERE XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
<=55;


or

DELETE NEW_ID_TABLE.*
FROM NEW_ID_TABLE
WHERE NEW_ID_TABLE.[@ID] IN
(SELECT XALUMNIARTLIST.[@ID]
FROM XALUMNIARTLIST
WHERE XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
<=55);


--

Ken Snell
<MS ACCESS MVP>

Okay, thank you. The table NEW_ID_TABLE is the one we want to delete
records
from based on the criteria in XALUMNIARTLIST.

DELETE NEW_ID_TABLE.*, NEW_ID_TABLE.FULL_NAME,
XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
FROM NEW_ID_TABLE INNER JOIN XALUMNIARTLIST ON NEW_ID_TABLE.[@ID] =
XALUMNIARTLIST.[@ID]
WHERE (((XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR)<=55));


--
MorningStarFan

That error occurs when Jet cannot identify the unique record that is to
be
deleted. Post the SQL statement that produces this error, along with an
explanation of which table's records are to be deleted, and we can assist
you with rewriting it to get around this problem.

--

Ken Snell
<MS ACCESS MVP>


message

 

[MorningStarFan]

Thank you! The second query worked. I had written similar queries myself
using SQLPLUS and ORACLE db but didn't think of trying a subquery here in
Access until I had already posted my question. I'm not familiar yet with
syntactical differences between SQLPLUS and the traditional Jet SQL syntax.
IS there a good reference manual somewhere that would have examples to answer
questions like this one?
--
MorningStarFan

One of these queries should do the trick for you:


DELETE NEW_ID_TABLE.*
FROM NEW_ID_TABLE
INNER JOIN XALUMNIARTLIST
ON NEW_ID_TABLE.[@ID] =
XALUMNIARTLIST.[@ID]
WHERE XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
<=55;


or

DELETE NEW_ID_TABLE.*
FROM NEW_ID_TABLE
WHERE NEW_ID_TABLE.[@ID] IN
(SELECT XALUMNIARTLIST.[@ID]
FROM XALUMNIARTLIST
WHERE XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
<=55);


--

Ken Snell
<MS ACCESS MVP>

Okay, thank you. The table NEW_ID_TABLE is the one we want to delete
records
from based on the criteria in XALUMNIARTLIST.

DELETE NEW_ID_TABLE.*, NEW_ID_TABLE.FULL_NAME,
XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
FROM NEW_ID_TABLE INNER JOIN XALUMNIARTLIST ON NEW_ID_TABLE.[@ID] =
XALUMNIARTLIST.[@ID]
WHERE (((XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR)<=55));


--
MorningStarFan

That error occurs when Jet cannot identify the unique record that is to
be
deleted. Post the SQL statement that produces this error, along with an
explanation of which table's records are to be deleted, and we can assist
you with rewriting it to get around this problem.

--

Ken Snell
<MS ACCESS MVP>


message
Hello,

We have two tables in a query and joined on ID for a one time data
project
and we need to delete some records from one of the tables. We are using
the
other table to identify our records to be deleted. Our delete query
won't
allow records to be deleted. We get the error message "Could not delete
from
specified tables (Error 3086)". ANy ideas? I can manually delete the
records
one by one...

 

[Ken Snell \(MVP\)]

For SQL queries in general, the book "SQL Queries for Mere Mortals" by
Michael Hernandez and John Viescas is good.

For differences between SQLPLUS and Jet SQL, I don't have any
recommendation, sorry.

--

Ken Snell
<MS ACCESS MVP>

Thank you! The second query worked. I had written similar queries myself
using SQLPLUS and ORACLE db but didn't think of trying a subquery here in
Access until I had already posted my question. I'm not familiar yet with
syntactical differences between SQLPLUS and the traditional Jet SQL
syntax.
IS there a good reference manual somewhere that would have examples to
answer
questions like this one?
--
MorningStarFan

One of these queries should do the trick for you:


DELETE NEW_ID_TABLE.*
FROM NEW_ID_TABLE
INNER JOIN XALUMNIARTLIST
ON NEW_ID_TABLE.[@ID] =
XALUMNIARTLIST.[@ID]
WHERE XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
<=55;


or

DELETE NEW_ID_TABLE.*
FROM NEW_ID_TABLE
WHERE NEW_ID_TABLE.[@ID] IN
(SELECT XALUMNIARTLIST.[@ID]
FROM XALUMNIARTLIST
WHERE XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
<=55);


--

Ken Snell
<MS ACCESS MVP>



message

Okay, thank you. The table NEW_ID_TABLE is the one we want to delete
records
from based on the criteria in XALUMNIARTLIST.

DELETE NEW_ID_TABLE.*, NEW_ID_TABLE.FULL_NAME,
XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR
FROM NEW_ID_TABLE INNER JOIN XALUMNIARTLIST ON NEW_ID_TABLE.[@ID] =
XALUMNIARTLIST.[@ID]
WHERE (((XALUMNIARTLIST.XPERSON_PRIMARY_DEG_YEAR)<=55));


--
MorningStarFan


:

That error occurs when Jet cannot identify the unique record that is
to
be
deleted. Post the SQL statement that produces this error, along with
an
explanation of which table's records are to be deleted, and we can
assist
you with rewriting it to get around this problem.

--

Ken Snell
<MS ACCESS MVP>


message
Hello,

We have two tables in a query and joined on ID for a one time data
project
and we need to delete some records from one of the tables. We are
using
the
other table to identify our records to be deleted. Our delete query
won't
allow records to be deleted. We get the error message "Could not
delete
from
specified tables (Error 3086)". ANy ideas? I can manually delete the
records
one by one...

 

[Cat33463]

I'm having this same problem. Can I send you the SQL statement that produced
the error and perhaps you can write a work-around?