SQL Server Update syntax
Posted on 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
, LOCATION_ID integer
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
THis same command does not seem to work with SQL Server.