Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Move Access update query to SQL Server

Posted on 2011-10-31
Medium Priority
Last Modified: 2012-05-12

I'm trying to convert an update query with an Inner Join created in Access to run on SQL Server.

In access it's
UPDATE tblCensus INNER JOIN tblFacilityInfo ON tblCensus.NHID = tblFacilityInfo.nhid SET tblCensus.Batch = 3
WHERE (((tblFacilityInfo.DateSched1)>#10/28/2011#));

Open in new window

Which works but when I paste it into an SQL Server query I get the following errors.

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'INNER'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'tblFacilityInfo'.

I did change my SQL Server query to deal with the DateTime field.

UPDATE tblCensus SET tblCensus.Batch = 3
INNER JOIN dbo.tblFacilityInfo ON dbo.tblCensus.NHID = dbo.tblFacilityInfo.nhid         
WHERE (((tblFacilityInfo.DateSched1)> '10/28/2011 00:00:00:000'));

Open in new window

How can I get this to work in SQL Server and why is it not now?  Thanks
Question by:eshurak
LVL 27

Accepted Solution

Shaun Kline earned 2000 total points
ID: 37059271
UPDATE tblCensus SET tblCensus.Batch = 3
FROM tblCensus INNER JOIN dbo.tblFacilityInfo ON dbo.tblCensus.NHID = dbo.tblFacilityInfo.nhid        
WHERE (((tblFacilityInfo.DateSched1)> '10/28/2011 00:00:00:000'));

Author Comment

ID: 37059359
So just missing the "From"  Thanks.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

580 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