Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Delete query not working

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
eshurak
Asked:
eshurak
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:

DELETE  *
FROM  tblFacilities
Where tblFacilities.nhid Not In (Select [Facility info].nhid From [Facility info])
0
 
csenseCommented:
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
 
csenseCommented:
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
 
ukerandiCommented:
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
 
ukerandiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now