CODEWITHSUNDEEP
mysql
Renan Aguiar
Renan Aguiar

Reputation: 245

How to update a table setting the where from a select from another table?

I have to update component_phase with the Cost from levelfour but also I have to join plancomponent to get the keys.

I think Im close to the answer, but it is not right.

component_phase
*idPlanComponent
costEstimate

plancomponent
*idPlanComponent
*LevelFourId
*idPlan


levelfour
*LevelFourId
Cost


UPDATE component_phase 
SET costEstimate = (SELECT Cost FROM levelfour, plancomponent 
    WHERE levelfour.LevelFourId = plancomponent.LevelFourId 
    AND idPlan = :idPlan) 
WHERE idPlanComponent in (SELECT idPlanComponent 
    FROM plancomponent 
    WHERE idPlan = :idPlan

Upvotes: 0

Views: 22

Answers (1)

Dark Knight
Dark Knight

Reputation: 6541

You can use MySQL UPDATE JOIN to do the update.

UPDATE component_phase cp
INNER JOIN plancomponent p ON cp.idPlanComponent = p.idPlanComponent
INNER JOIN levelfour l ON l.LevelFourId = p.LevelFourId
SET cp.costEstimate = l.Cost
WHERE p.idPlan = :idPlan;

Upvotes: 2

Related Questions