could not run delete query

  • Thread starter Thread starter xg
  • Start date Start date
X

xg

I have a select query that works fine as follows:

SELECT StudAddress.*
FROM StudAddress LEFT JOIN Student ON StudAddress.SID = Student.SID
WHERE (((Student.SID) Is Null));

However, when I changed it to delete query, it does not run:

DELETE StudAddress.*, Student.SID
FROM StudAddress LEFT JOIN Student ON StudAddress.SID = Student.SID
WHERE (((Student.SID) Is Null));

It reported: Could not delete from specified table (Error 3086)
It has OK and Help button. The help button says the table is read only. I'm
on stand alone computer and I opened database normally. My other delete
query works but not this one. Any idear?

I just want to delete records in StudAddress table where SID is not found in
Student table.

Thanks.
 
Try changing the SID field from the Student table to:
Where

Or, you might have more success with a subquery:

DELETE FROM StudAddress
WHERE NOT EXISTS
(SELECT SID FROM Student
WHERE Student.SID = StudAddress.SID);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
xg said:
I have a select query that works fine as follows:

SELECT StudAddress.*
FROM StudAddress LEFT JOIN Student ON StudAddress.SID = Student.SID
WHERE (((Student.SID) Is Null));

However, when I changed it to delete query, it does not run:

DELETE StudAddress.*, Student.SID
FROM StudAddress LEFT JOIN Student ON StudAddress.SID = Student.SID
WHERE (((Student.SID) Is Null));

It reported: Could not delete from specified table (Error 3086)
It has OK and Help button. The help button says the table is read only. I'm
on stand alone computer and I opened database normally. My other delete
query works but not this one. Any idear?

I just want to delete records in StudAddress table where SID is not found in
Student table.
Click to expand...

PMFBI

DELETE DISTINCTROW StudAddress.*
FROM StudAddress LEFT JOIN Student
ON StudAddress.SID = Student.SID
WHERE (((Student.SID) Is Null));

Delete Queries (KB207761)
***quote***
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.

However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.

To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query.

***unquote***
 
Back
Top