We help IT Professionals succeed at work.
Get Started

SQL Server update query syntax

Dale Fye
Dale Fye asked
on
293 Views
Last Modified: 2014-08-21
I'm coming from the Access world, with a lot of experience working with SQL Server via ODBC connections, but am migrating an application to SQL Server and would like to migrate some of the queries over to SQL Stored Procedures.

Background.  I have a staging table in SQL Server, where I'm dumping data from Access and several other data sources.  From that table I have created a view (vw_Cygnet_Tank_Readings_Inches) which pulls in a specific subset of the data from the staging table.

There is a second view (vw_Equipment_Tanks) which joins my Equipment and Tanks tables to provide access to the tank Diameter field based on the EquipID.  I have to join this view to the previous view to associate the Equip_ID field and Diameter field to the tanks identified in the previous view.

I have a destination table in SQL Server (tbl_Readings_Tanks)  which contains about 15 fields, but for the purpose of this question, I need to update two of those fields.  The UPDATE syntax in SQL Server is different in SQL Server than in Access, and I just cannot seem to get this right.
USE WHR_System_Tables

DECLARE @AsOf as datetime2(7)

SELECT @AsOf = NULL

UPDATE tbl_Readings_Tanks 
SET tbl_Readings_Tanks.Inches_End = T.Inches
, tbl_Readings_Tanks.Closing_Vol = (CTRI.Inches/12) * (3.14159 * (ET.Diameter/2) * (ET.Diameter/2)) * 0.17811
FROM (
SELECT ET.Equip_ID
, CTRI.FacilityID
, CTRI.DT_Recorded
, CTRI.Product_ID
, CTRI.TankNum
, CTRI.Inches
, ET.Diameter
, Closing_Vol = (CTRI.Inches/12) * (3.14159 * (ET.Diameter/2) * (ET.Diameter/2)) * 0.17811
FROM  dbo.vw_Equipment_Tanks as ET
INNER JOIN dbo.vw_Cygnet_Tank_Readings_Inches as CTRI
ON ET.DS_PK_Text = CTRI.FacilityID 
AND ET.Product_ID = CTRI.Product_ID 
AND ET.Tank_Num = CTRI.TankNum
WHERE (@AsOf IS NULL) OR (CTRI.Dt_recorded > @AsOf)
) as T
INNER JOIN tbl_Readings_Tanks as RT
ON T.Equip_ID = RT.Equip_ID
AND T.DT_Recorded = RT.docDate
WHERE RT.Inches_End <> T.Inches 

Open in new window

I've declared an @AsOf variable because I will eventually put this in a stored procedure and pass that value as a parameter.  At the moment, it is set to NULL to select all of the records from vw_Cygnet_Tank_Readings_Inches.
Comment
Watch Question
Top Expert 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE