[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server Update syntax

Posted on 2012-09-10
5
Medium Priority
?
478 Views
Last Modified: 2012-09-10
Can someone please provide me with the correct syntax for updating a table in SQL Server?  Specifically, here is what I am trying to do:

I have 1 table with columns like this:

create TABLE #sov
(
  ID       nvarchar(255)
, LOCATION_ID               integer
,BUILDING_ID                integer
,BUILDING_TIV               money
,LOCATION_TIV               money
);

I have values in all fields except LOCATION_TIV.  The first 3 fields form a unique key.  One location_id can have many building_ids (location is parent of building).  

I want to populate the LOCATION_TIV field based on summing the values for all records that match on ID and LOCATION_ID.  Essentially, the LOCATION_TIV field is a field that summarizes all of the BUILDING_TIV values for the matching location.

Can someone tell me how to do this?

For reference, here is how I would do the same thing using ORACLE syntax:

update #sov v1
set v1.LOCATION_TIV = (select sum(BUILDING_TIV) FROM #sov v2
                                      where v2.ID=v1.ID
                                      and v2.LOCATION_ID=v1.LOCATION_ID)

THis same command does not seem to work with SQL Server.
0
Comment
Question by:jbaird123
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 38382976
update  v1
set v1.LOCATION_TIV = (select sum(BUILDING_TIV) FROM #sov v2
                                      where v2.ID=v1.ID
                                      and v2.LOCATION_ID=v1.LOCATION_ID)
FROM #sov v1
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38382979
try
  update v1
  set v1.LOCATION_TIV = (select sum(BUILDING_TIV) FROM #sov v2 
                              where v2.ID=v1.ID
                              and v2.LOCATION_ID=v1.LOCATION_ID)
  from #sov v1

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38382986
please try this:
update v1
set v1.LOCATION_TIV = (select sum(BUILDING_TIV) FROM #sov v2
                                      where v2.ID=v1.ID
                                      and v2.LOCATION_ID=v1.LOCATION_ID)
FROM  #sov v1

Open in new window


http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38383024
answers posted, retracted
0
 

Author Closing Comment

by:jbaird123
ID: 38383041
Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

868 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