Update query problem - How to use select stmt

[hedgracer]

Here is what I am trying to do. I have a table variable :

DECLARE @NITable table
(
Reg Varchar(1),
Dept Varchar(3),
CurrentMonth Numeric(18,0),
CurrentYTD Numeric(18,0),
CurrentAvg Numeric(18,0),
PriorYTD Numeric(18,0),
PriorAvg Numeric(18,0)
)

The reg and dept is populated from two tables. Current year reg and dept is first inserted and then previous year's reg and dept is inserted using an unmatched query. I need to update Current Month with the net income from the current year's data matching the current year table's net income by reg and dept. Therein is my problem. I tried to set up the following query:

update @NITable
SET CurrentMonth =(select CAST(SUM([Sep])AS NUMERIC(18,0))from
[Actg_FPG_Reg_All_Deps_Report_NI]
where @NITable.reg = [Actg_FPG_Reg_All_Deps_Report_NI].Reg and @NITable.Dept =
[Actg_FPG_Reg_All_Deps_Report_NI].dept )

SQL Server 2005 is throwing an error on the where clause that @NITable is not declared. Here is all of my code:


DECLARE @NITable table
(
Reg Varchar(1),
Dept Varchar(3),
CurrentMonth Numeric(18,0),
CurrentYTD Numeric(18,0),
CurrentAvg Numeric(18,0),
PriorYTD Numeric(18,0),
PriorAvg Numeric(18,0)
)

Declare @NIGrouped Table
(
Reg Varchar(1),
Dept Varchar(3)
)

insert into @NIGrouped
(
Reg,
Dept
)
select Reg, Dept from Actg_FPG_Reg_All_Deps_Report_NI
group by Reg, Dept

insert into @NIGrouped
(
Reg,
Dept
)
select Reg, Dept from Actg_FPG_Reg_All_Deps_Report_2010
group by Reg, Dept

insert into @NITable
(
Reg,
Dept
)
select Reg, Dept from @NIGrouped
group by Reg, Dept

update @NITable
SET CurrentMonth =(select CAST(SUM([Sep])AS NUMERIC(18,0))from
[Actg_FPG_Reg_All_Deps_Report_NI]
where @NITable.reg = [Actg_FPG_Reg_All_Deps_Report_NI].Reg and @NITable.Dept =
[Actg_FPG_Reg_All_Deps_Report_NI].dept )

I am not sure if the last update query is even right or why I am getting the error message. Can someone help me on these two items? Any help is greatly appreciated. Thanks.

 

[gmmastros]

There seems to be 2 points of confusion for you.

1. When you use a table variable in a query with multiple tables, you need to use a table alias. For Example:

[tt]
Select Alias.Column1,
Alias.Column2,
TableName.ColumnX
From @TableVariable [!]As Alias[/!]
Inner Join TableName
On Alias.Column7 = TableName.Column7
[/tt]

When you use a table alias like this, you need make sure you use it consistently throughout the rest of the query.

2. When you want to update one table based on a sum, you usually need to use a derived table where you calculate the sum and then join the derived table to the other table.

Ex:

[tt]
Update Table1
Set Table1.ColumnX = DerivedTableAlias.SumOfBlah
From Table1
Inner Join (
Select ColumnToJoin,
Sum(Blah) As SumOfBlah
From OtherTable
Group By ColumnToJoin
) As DerivedTableAlias
On Table1.ColumnToJoin = DerivedTableAlias.ColumnToJoin
[/tt]

Putting both concepts together...

update NITable
SET CurrentMonth = A.SumOfSep
From   @NITable As NITable
       Inner Join (
         select [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
                [Actg_FPG_Reg_All_Deps_Report_NI].dept,
                CAST(SUM([Sep])AS NUMERIC(18,0)) As SumOfSep
         from   [Actg_FPG_Reg_All_Deps_Report_NI] 
         Group By [Actg_FPG_Reg_All_Deps_Report_NI].Reg,
                [Actg_FPG_Reg_All_Deps_Report_NI].dept
         ) As A
         On NITable.Reg = A.Reg
         And NITable.Dept = A.Dept

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

For what it's worth, I have some comments regarding other aspects of your code. I am willing to share these comments, but only if you are interested in them. Let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom