Move Access update query to SQL Server

Posted on 2011-10-31
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 25

    Accepted Solution

    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'));
    LVL 3

    Author Comment

    So just missing the "From"  Thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    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…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Viewers will learn how the fundamental information of how to create a table.

    737 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

    15 Experts available now in Live!

    Get 1:1 Help Now