?
Solved

SQL Server update query syntax

Posted on 2014-08-21
4
Medium Priority
?
203 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.
0
Comment
Question by:Dale Fye
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40277783
You need to use this syntax:
USE WHR_System_Tables

DECLARE @AsOf as datetime2(7)

SELECT @AsOf = NULL

UPDATE RT
SET RT.Inches_End = T.Inches
, RT.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

Basically, because you have assigned the RT alias to the table in the FROM clause, you need to use it in your UPDATE clause
0
 
LVL 49

Author Closing Comment

by:Dale Fye
ID: 40277995
I should have thought of that!

How would I go about getting the number of records updated after running that update query?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40277999
Very easy. Use @@ROWCOUNT system function to get the number of affected rows
DECLARE @HowManyRowsUpdated INT
UPDATE table SET value = 1
SELECT @HowManyRowsUpdated = @@ROWCOUNT

Open in new window

0
 
LVL 49

Author Comment

by:Dale Fye
ID: 40278005
Follow-up question here
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

840 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