How to UPDATE a field using INNER JOIN...Access query works.  Need to convert to SQL Server.

Posted on 2007-08-09
Last Modified: 2008-01-09
Its been a long time since Ive been to EE.  The place looks great.

Im very new to SQL server.  I'm using Microsoft SQL Enterprise Manager Version: 8.0

The task at hand is a data conversion.  One part is to make the contents in a field (cLLCClss) that is NB or NA become NP.  Part of doing this will be deleting one of two entitys (nLLCEntId) records that has two records with these two classes, NB and NA.  The one with NA stays and the one with NB goes away.

So I thought Id update another field (cLLCInsUser) in the records that meet the criteria for deletion with ZZ  Then I could delete those records based on ZZ as the criteria.

I created a table (tblLLicenseClassNANB) that contains those records with only one field (nLLCEntId) in it.  I join that to the actual table (dbo_tblLLicenseClass) that will be the target of the update.  In select, it returns what I want.  When I put the NB in as criteria it returns the exact records I want to update.

This works in Access and looks like this:

UPDATE tblLLicenseClassNANB INNER JOIN dbo_tblLLicenseClass ON tblLLicenseClassNANB.nLLCEntId = dbo_tblLLicenseClass.nLLCEntId SET dbo_tblLLicenseClass.cLLCInsUser = "ZZ"
WHERE (((dbo_tblLLicenseClass.cLLCClss)="NB"));

In SQL the select statement looks like this:

SELECT     *, tblLLicenseClass.cLLCClss AS Expr1
FROM         tblLLicenseClass INNER JOIN
                      tblLLicenseClassNANB ON tblLLicenseClass.nLLCEntId = tblLLicenseClassNANB.nLLCEntId
WHERE     (tblLLicenseClass.cLLCClss = 'NB')

I attempted updating it this way but it returns an error (near INNER):

UPDATE    tblLLicenseClass INNER JOIN
                      tblLLicenseClassNANB ON tblLLicenseClass.nLLCEntId = tblLLicenseClassNANB.nLLCEntId
SET              cLLCInsUser = 'ZZ'
WHERE     (tblLLicenseClass.cLLCClss = 'NB')

Would someone tell me how to update this field using the INNER JOIN or convert the Access SQL statement to a SQL Server SQL statement?

I hope I'm posting the question in the right place.  And I don't remember if 100 pts. is about right.


Question by:Icebreak
    LVL 42

    Accepted Solution

    UPDATE    tblLLicenseClass
    SET              cLLCInsUser = 'ZZ'
    tblLLicenseClass INNER JOIN
                          tblLLicenseClassNANB ON tblLLicenseClass.nLLCEntId = tblLLicenseClassNANB.nLLCEntId
    WHERE     (tblLLicenseClass.cLLCClss = 'NB')

    Author Comment

    I got "The Query Designer does not support the Optional FROM clause SQL construct."

    I may have posed the question wrong.  I'm in SQL Server Enterprise Manager in a query.

    Anyway, I just ran it and it shifted the spacing around like this:

    UPDATE    tblLLicenseClass
    SET              cLLCInsUser = 'ZZ'
    FROM         tblLLicenseClass INNER JOIN
                          tblLLicenseClassNANB ON tblLLicenseClass.nLLCEntId = tblLLicenseClassNANB.nLLCEntId
    WHERE     (tblLLicenseClass.cLLCClss = 'NB')

    And it ran perfectly.

    Thanks a lot.  That will really get me down the road.  Very much appreciated.

    I'll go tap that 'accept as answer' button now.

    Author Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    Different between these 2 sql statements 12 50
    SSRS  integration 5 70
    create index to c1, c2 and c3 9 46
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    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

    17 Experts available now in Live!

    Get 1:1 Help Now