You are here:

MS SQL/help with sql statement

Advertisement


Question
I am new to sql .I have the following sql query in Vb.net and ms access  


SELECT "Table1" AS Table, SUM(a) - SUM(b) AS Result FROM table1
UNION
SELECT "Table2", SUM(a) - SUM(b) FROM table2
UNION
SELECT "Table3", SUM(a) - SUM(b) FROM table3

It gives me this output
table  | result
-------|--------   
  

I would like to know how do I update the below table with results column from output table?
name  |   table |   result
------|-------|--------
     
     

I have tried to save the output table in a new table then reference the new table to update the other table. I get Error

select * into NewTable
from
(
 SELECT 'Table1' AS [Table], SUM(a) - SUM(b) AS Result FROM table1 union all
 SELECT 'Table2' AS [Table], SUM(a) - SUM(b) AS Result FROM table2
)

can you please tell me how can i solve it ?

Answer
Hi Dany, you approach is right...can you share your error, try following...

UPDATE OtherTableYouhave
SET ColumnName = final.Result
--SELECT * --This is just to verify before Update
FROM
(
SELECT "Table1" AS Table, SUM(a) - SUM(b) AS Result FROM table1
UNION ALL
SELECT "Table2", SUM(a) - SUM(b) FROM table2
UNION ALL
SELECT "Table3", SUM(a) - SUM(b) FROM table3
) as Final
INNER JOIN OtherTableYouhave o on Final.Table = o.Table

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mohit Nayyar

Expertise

Microsoft SQL Server: T-SQL development (stored procedures, triggers, functions), Database optimization, performance tuning, high availability (Clustering, Log Shipping, Mirroring, Replication), scalability, SQL Server migration (Sybase/Oracle to SQL Server), SSIS/DTS, Data Warehousing (Kimball Methodology), ETL (SSIS), Cube (SSAS), Reporting Services (SSRS), database modeling, database administration, Security implementation and typically anything related to Microsoft SQL Server.

Experience

With over 10 years of experience in Database Administration, Development, Business Intelligence solutions and managing enterprise level database solutions based on Microsoft SQL Server.
Publications
IT Magazine: ASP, SQL

Publications
IT Magazine: ASP, SQL
SQLServerCentral.com

Education/Credentials
MCA (Masters)

Awards and Honors
Brainbench Certified: RDBMS
MCTS: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCITP: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCDBA: SQL Server 2000
MCP: SQL Server 6.5

©2016 About.com. All rights reserved.