Solved

Delete query not working

Posted on 2011-09-13
5
318 Views
Last Modified: 2012-06-27
When trying to run the query below I get a "Could not delete from specified tables" message.    I'm using Access 2010 and SQL Server 2005.  [Facility Info] is a linked Excel spreadsheet.  I am trying to delete records in tblFacilites that are not in the linked spreadsheet.  Any ideas on how to do this would be great.  

Thanks,

DELETE  tblFacilities.* 
FROM [Facility info] RIGHT JOIN tblFacilities ON [Facility info].nhid = tblFacilities.nhid
WHERE ((([Facility info].nhid) Is Null));

Open in new window

0
Comment
Question by:eshurak
  • 2
  • 2
5 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 36533509

DELETE  *
FROM  tblFacilities
Where tblFacilities.nhid Not In (Select [Facility info].nhid From [Facility info])
0
 
LVL 1

Expert Comment

by:csense
ID: 36534192
you don't need to specify any fields after DELETE (e.g. *) because DELETE deletes everything on that row.

So query should be:
DELETE
FROM  tblFacilities 
Where tblFacilities.nhid Not In (Select [Facility info].nhid From [Facility info])

Open in new window

0
 
LVL 1

Expert Comment

by:csense
ID: 36534227
OR:

DELETE 
FROM tblFacilities
	FROM tblFacilities AS a
		LEFT JOIN [Facility Info] AS b ON a.nhid = b.nhid
WHERE b.nhid IS NULL;

Open in new window

0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36535395
Write below code in Query and run

Select *
FROM [Facility info] RIGHT JOIN tblFacilities ON [Facility info].nhid = tblFacilities.nhid
WHERE [Facility info].nhid) Is Null;

check are any data in the query,some time you are tring to delete records not in the baove criteria

then
run below code
Delete
FROM [Facility info] RIGHT JOIN tblFacilities ON [Facility info].nhid = tblFacilities.nhid
WHERE [Facility info].nhid) Is Null;
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 36535404
I presume You are using Right Join b'cos "The RIGHT JOIN keyword returns all the rows from the right table (tblFacilities), even if there are no matches in the left table (Facility info)."

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now