Help needed on update statement

Question

Post reply

Help needed on update statement

Best-330874

SSCrazy

Points: 2350
More actions
March 8, 2007 at 9:28 pm

#117891

Hi ,
    i need help on this. i have two tables
1.COST_Project_Phase_Uniformat
     which has the following three columns :    MS_ID,Project_ID,Uniformat_Cost
2.COST_Project_Phase
     which has the following three columns :    MS_ID,Project_ID,Construction_Cost

what i want to do is pass parameters MS_ID AND Project_ID
AND UPDATE Column Construction_Cost in COST_Project_Phase table from COST_Project_Phase_Uniformat table from column Uniformat_Cost

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

CREATE PROCEDURE PR_YOURPROCNAME(=@MS_ID INT,=@PROJECT_ID INT) AS

UPDATE COST_PROJECT_PHASE

SET COST_PROJECT_PHASE.CONSTRUCTION_CONST = COST_PROJECT_PHASE_UNIFORMAT

FROM COST_PROJECT_PHASE_UNIFORMAT

WHERE COST_PROJECT_PHASE.MS_ID = COST_PROJECT_PHASE_UNIFORMAT.MS_ID

AND COST_PROJECT_PHASE.PROJECT_ID = COST_PROJECT_PHASE_UNIFORMAT.PROJECT_ID

AND COST_PROJECT_PHASE.MS_ID=@MS_ID

ANDCOST_PROJECT_PHASE.PROJECT_ID =@PROJECT_ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

It gives me an error message when i execute it.

Msg 207, Level 16, State 1, Procedure COSTUpdateProjectPhaseHeader, Line 17

Invalid column name 'COST_Project_Phase_Uniformat'.

CREATE PROCEDURE PR_YOURPROCNAME(=@MS_ID INT,=@PROJECT_ID INT) AS

UPDATE COST_PROJECT_PHASE

SET COST_PROJECT_PHASE.CONSTRUCTION_CONST = COST_PROJECT_PHASE_UNIFORMAT

FROM COST_PROJECT_PHASE_UNIFORMAT

WHERE COST_PROJECT_PHASE.MS_ID = COST_PROJECT_PHASE_UNIFORMAT.MS_ID

AND COST_PROJECT_PHASE.PROJECT_ID = COST_PROJECT_PHASE_UNIFORMAT.PROJECT_ID

AND COST_PROJECT_PHASE.MS_ID=@MS_ID

ANDCOST_PROJECT_PHASE.PROJECT_ID =@PROJECT_ID

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 3

SET COST_PROJECT_PHASE.CONSTRUCTION_CONST = COST_PROJECT_PHASE_UNIFORMAT.UNIFORMAT_COST

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

Thanks for the help but can you tell me one more thing in this proc.

what i want to do is to sum the Uniformat_Cost in the COST_PROJECT_PHASE_UNIFORMAT table and then do this update in the Contruction_Cost in COST_PROJECT_PHASE table

Lowell SSC Guru Points: 323518 More actions · Answer 5

I consider it a bad practice to insert summary data into another table...it is very, very possible the data could be out of sync.

you should simly use a VIEW instead.

CREATE VIEW YOURVIEWNAME AS

SELECT

SUM(UNIFORMAT_COST) AS CONSTRUCTION_CONST,

MS_ID,

PROJECT_ID

FROM COST_PROJECT_PHASE_UNIFORMAT

GROUP BY

MS_ID,

PROJECT_ID

if you have to update the table, this should get you started:

UPDATE COST_PROJECT_PHASE

SET COST_PROJECT_PHASE.CONSTRUCTION_CONST = X.UNIFORMAT_COST

FROM (SELECT

SUM(UNIFORMAT_COST) AS CONSTRUCTION_CONST,

MS_ID,

PROJECT_ID

FROM COST_PROJECT_PHASE_UNIFORMAT

GROUP BY

MS_ID,

PROJECT_ID

) X --virtual table X..a subselect containing the data desired.

WHERE COST_PROJECT_PHASE.MS_ID = X.MS_ID

AND COST_PROJECT_PHASE.PROJECT_ID = X.PROJECT_ID

AND COST_PROJECT_PHASE.MS_ID =@MS_ID

ANDCOST_PROJECT_PHASE.PROJECT_ID =@PROJECT_ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!