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 2000 Forums  Transact-SQL (2000)  Update Query

Author  Topic  SirFang Starting Member 6 Posts Posted - 2006-02-28 : 19:27:13 I am making some adjustments to our Data warehouse program. We have three lines of business, Telephony, cable and Internet. As we add a new kind of Telephone service, VOIP we want to just combine the data for both types of Phone. We have always just inserted the data but I am having trouble with the fact that data is coming from two sources but get the same product ID. INSERT INTO AllTCRep ( TECH_, TOTAL_TCS, TOTAL_REPEAT, REFERRALS, PPWEDate, Product ) SELECT Ttcrep.TECH, Ttcrep.TOTAL_TCS, Ttcrep.TELE_REPEA, Ttcrep.TELE_REFER, SystemValues.ImportDate, 2 AS Product FROM Ttcrep, SystemValues WHERE (((Ttcrep.TECH) Is Not Null)); PK is TECH_, PPWEDATE, and Product What I need to do is insert the fisrt set of data then do an update the second go round. This is what I have tried but I am not an expert at writing queries and I keep getting various errors. UPDATE AllTCRep (TOTAL_TCS, Total_REPEAT ) SET Alltcrep.Total_Tcs = (Alltcrep.Total_Tcs + ctcrep.TOTAL_CDV_), AllTcrep.Total_Repeat = (AllTcrep.Total_Repeat + ctcrep.CDV_REPEAT) FROM( SELECT Ctcrep.IR_TECH_OJ, Ctcrep.TOTAL_CDV_, Ctcrep.CDV_REPEAT, AlltcRep.TECH_, AlltcRep.TOTAL_TCS, AlltcRep.TOTAL_REPEAT, Systemvalues.ImportDate, AlltcRep.PPWEDate, AlltcRep.Product FROM Ctcrep, AlltcRep, Systemvalues WHERE ((AlltcRep.TECH_)=Ctcrep.IR_TECH_OJ) AND (Systemvalues.ImportDate=alltcrep.ppwedate) AND (AlltcRep.Product=2)) ) These queries are being run from Access 2003 on a MSSQL 2000 server.