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

x
?
Solved

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

Posted on 2007-08-09
3
Medium Priority
?
4,028 Views
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.

TIA

0
Comment
Question by:Icebreak
  • 2
3 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 19664657
UPDATE    tblLLicenseClass
SET              cLLCInsUser = 'ZZ'
FROM
tblLLicenseClass INNER JOIN
                      tblLLicenseClassNANB ON tblLLicenseClass.nLLCEntId = tblLLicenseClassNANB.nLLCEntId
WHERE     (tblLLicenseClass.cLLCClss = 'NB')
0
 

Author Comment

by:Icebreak
ID: 19664871
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.
0
 

Author Comment

by:Icebreak
ID: 19664891
Outstanding!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

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