Help with update query

Posted on 2008-10-29
Last Modified: 2012-05-05
This has to got to be so simple but I can't get it to work....
I am trying to update 2 fields per record based on the results of a query. I can run the query by itself and it returns the data but when i add the update piece it balks about a ")" issue. I can't seem to find out why or where to put the closing ")". What am I missing???

use netperfmon
UPDATE dbo.nodes
set totalports = c.totalports,
from (
SELECT c.totalports, c.freeports
FROM [MyServer]...[truecontrol.rn_device] as C
JOIN dbo.nodes as N
on N.caption = c.hostname)
Question by:edrz01
LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 125 total points
ID: 22830235
this will work better:
use netperfmon
  set totalports = c.totalports,
from dbo.nodes n
join [MyServer]...[truecontrol.rn_device] as C
  on N.caption = c.hostname

Open in new window


Author Closing Comment

ID: 31511116
Once again, right on.  Looks like I need to spend a little more time on my SQL sysntax review....

Expert Comment

ID: 22830291
c is not visible from outside of the brackets, and the update clause expects an alias of the derived table

try this

use netperfmon
UPDATE dbo.nodes
set totalports = tt.totalports,
from (
SELECT c.totalports, c.freeports
FROM [MyServer]...[truecontrol.rn_device] as C
JOIN dbo.nodes as N
on N.caption = c.hostname) tt

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

895 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