Delete Query Error

S

Smallville_Kent

I have table "closures" that I am trying to run a delete query against. I am
trying to use a pass-through query (connected to Oracle) as a dynamic filter
so I am only deleting pertinent records. I get "Could not delete from
specified tables" error. I have read through similar questions and cannot
seem to get this to work even with a sub-query.

This is my original:

DELETE closures.*
FROM 00monthly_run_range INNER JOIN closures ON [00monthly_run_range].YYYYMM
= closures.yyyymm_cls;

This is my attempt using a sub-query:

DELETE FROM closures.*
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

The sub-query gives the following error: "Syntax error in query. Incomplete
query clause."

I have been stuck on this all day and have not been able to resolve. Thanks!
 
J

John Spencer

That does not look like a pass-through query. Have you tried?

DELETE
FROM closures
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

Interesting field names

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
S

Smallville_Kent

When I plug this in, I get a syntax error. "00monthly_run_range" is a
pass-through query I use to create my dynamic delete range. The process is
essentially a wipe and replace. In my world, history within a month can
change, thus the need for the delete.

The problem shouldn't be with the field names, should it?


Thanks.



John Spencer said:
That does not look like a pass-through query. Have you tried?

DELETE
FROM closures
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

Interesting field names

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Smallville_Kent said:
I have table "closures" that I am trying to run a delete query against. I am
trying to use a pass-through query (connected to Oracle) as a dynamic filter
so I am only deleting pertinent records. I get "Could not delete from
specified tables" error. I have read through similar questions and cannot
seem to get this to work even with a sub-query.

This is my original:

DELETE closures.*
FROM 00monthly_run_range INNER JOIN closures ON [00monthly_run_range].YYYYMM
= closures.yyyymm_cls;

This is my attempt using a sub-query:

DELETE FROM closures.*
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

The sub-query gives the following error: "Syntax error in query. Incomplete
query clause."

I have been stuck on this all day and have not been able to resolve. Thanks!
Click to expand...
Click to expand...
 
S

Smallville_Kent

If I turn the results of the pass-through into a table and plug that table
into the sub-query, it works. However, even if I try to run the delete off of
a select query run from the pass-through, the process does not work.

Thanks.



Smallville_Kent said:
When I plug this in, I get a syntax error. "00monthly_run_range" is a
pass-through query I use to create my dynamic delete range. The process is
essentially a wipe and replace. In my world, history within a month can
change, thus the need for the delete.

The problem shouldn't be with the field names, should it?


Thanks.



John Spencer said:
That does not look like a pass-through query. Have you tried?

DELETE
FROM closures
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

Interesting field names

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Smallville_Kent said:
I have table "closures" that I am trying to run a delete query against. I am
trying to use a pass-through query (connected to Oracle) as a dynamic filter
so I am only deleting pertinent records. I get "Could not delete from
specified tables" error. I have read through similar questions and cannot
seem to get this to work even with a sub-query.

This is my original:

DELETE closures.*
FROM 00monthly_run_range INNER JOIN closures ON [00monthly_run_range].YYYYMM
= closures.yyyymm_cls;

This is my attempt using a sub-query:

DELETE FROM closures.*
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

The sub-query gives the following error: "Syntax error in query. Incomplete
query clause."

I have been stuck on this all day and have not been able to resolve. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

One more attempt

DELETE DISTINCTROW Closures.*
FROM closures
WHERE closures.yyyymm_cls IN
(SELECT yyyymm FROM 00monthly_run_range)

As always, back up your data first so you can recover if this does not
delete the records as you wish.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Smallville_Kent said:
If I turn the results of the pass-through into a table and plug that table
into the sub-query, it works. However, even if I try to run the delete off
of
a select query run from the pass-through, the process does not work.

Thanks.



Smallville_Kent said:
When I plug this in, I get a syntax error. "00monthly_run_range" is a
pass-through query I use to create my dynamic delete range. The process
is
essentially a wipe and replace. In my world, history within a month can
change, thus the need for the delete.

The problem shouldn't be with the field names, should it?


Thanks.



John Spencer said:
That does not look like a pass-through query. Have you tried?

DELETE
FROM closures
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

Interesting field names

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Smallville_Kent wrote:
I have table "closures" that I am trying to run a delete query
against. I am
trying to use a pass-through query (connected to Oracle) as a dynamic
filter
so I am only deleting pertinent records. I get "Could not delete from
specified tables" error. I have read through similar questions and
cannot
seem to get this to work even with a sub-query.

This is my original:

DELETE closures.*
FROM 00monthly_run_range INNER JOIN closures ON
[00monthly_run_range].YYYYMM
= closures.yyyymm_cls;

This is my attempt using a sub-query:

DELETE FROM closures.*
WHERE EXISTS
(SELECT yyyymm FROM 00monthly_run_range
WHERE 00monthly_run_range.yyyymm = closures.yyyymm_cls);

The sub-query gives the following error: "Syntax error in query.
Incomplete
query clause."

I have been stuck on this all day and have not been able to resolve.
Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Top