Updating a Table from another Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Updating a Table from another Table

Author

Topic

Brian C
Starting Member

10 Posts

Posted - 2009-02-23 : 15:28:03

Hi Everyone,

I was hoping someone could help me with the following problem.

I am writing code to populate the fact table of an aggregate database. That part is working fine but now I need to add a column for PrviousTotalApplications. This column is the TotalApplications of the previous month. I was hoping that I could simply select the TotalApplications from the prior month (the 1562 is a hardcoded MonthID from the previous month. In the finished code it will simply be current month -1) and use a WHERE clause that includes all the Dimension Foreign Keys. Same in the WHERE EXISTS. Basically I am searching for the last month where all the DimIDs are the same as whats in the temp table.

The temp table is used to hold the current month’s values and FKs. I then add the prior month totals then insert the table into the database.

So below all the Dim tables are the dimension table foreign keys and the totals are self explanatory.

I hope I have provided enough detail. Again I am just trying to get the prior months totalapplications and update the temp table row that has the same dimensional foreign keys.

Thanks for any help

Brian

************************************************************************************

update #TempApplication1
set PreviousTotalApplications = (select f.TotalApplications from fct_application f where f.Dim_TradeID = #TempApplication1.TradeID
and f.Dim_MonthID = 1562
and f.Dim_DemographicID = #TempApplication1.Aboriginal
and f.Dim_ApplicationTypeid = #TempApplication1.AppTypeID
and f.dim_officeid = #TempApplication1.officeid
and f.dim_applicationStatusid = #TempApplication1.Appstatusid
and f.dim_technicaltrainingLevelID = #TempApplication1.technicaltraininglevelid
and f.dim_tradeperiodID = #TempApplication1.tradeperiodtypeid
and f.dim_accreditationid = #TempApplication1.accreditationid)
where exists(select f.TotalApplications,f.Dim_MonthID, f.Dim_DemographicID,f.Dim_ApplicationTypeid,f.dim_officeid
,f.dim_applicationStatusid,f.dim_technicaltrainingLevelID, f.dim_tradeperiodID, f.dim_accreditationid

from Fct_Application f
where f.Dim_TradeID = #TempApplication1.TradeID
and f.Dim_MonthID = 1562
and f.Dim_DemographicID = #TempApplication1.Aboriginal
and f.Dim_ApplicationTypeid = #TempApplication1.AppTypeID
and f.dim_officeid = #TempApplication1.officeid
and f.dim_applicationStatusid = #TempApplication1.Appstatusid
and f.dim_technicaltrainingLevelID = #TempApplication1.technicaltraininglevelid
and f.dim_tradeperiodID = #TempApplication1.tradeperiodtypeid
and f.dim_accreditationid = #TempApplication1.accreditationid)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-23 : 16:57:22

Are you sure you need where exists there ??? I'm thinking you just need this,,

update #TempApplication1 
set #TempApplication1.PreviousTotalApplications = f.TotalApplications
from Fct_Application f,#TempApplication1 
where f.Dim_TradeID = #TempApplication1.TradeID
and f.Dim_MonthID = 1562
and f.Dim_DemographicID = #TempApplication1.Aboriginal
and f.Dim_ApplicationTypeid = #TempApplication1.AppTypeID
and f.dim_officeid = #TempApplication1.officeid
and f.dim_applicationStatusid = #TempApplication1.Appstatusid
and f.dim_technicaltrainingLevelID = #TempApplication1.technicaltraininglevelid
and f.dim_tradeperiodID = #TempApplication1.tradeperiodtypeid
and f.dim_accreditationid = #TempApplication1.accreditationid

Brian C
Starting Member

10 Posts

Posted - 2009-02-23 : 17:20:21

Hi There,

I think it worked. I changed the total for a prior month and it showed up with the current total and the prior month total in the appropriate columns. Just out of curiousity can you explain why I was getting the error I was because I was using the exist?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources