MySQL: "Unknown column in where clause" during Update Statement

Question

UPDATE Recipes RE, (
    SELECT SUM((((i.iCaseCost/i.iCaseQty)/i.iUnitSize)/i.iUnitSoldBy)*ri.riQty*ri.riMeasureBy) AS     'RecipeCost'
      FROM Recipes r INNER JOIN RecipeIngredients ri
        ON r.rID = ri.rID JOIN Ingredients i
        ON ri.iID = i.iID 
        WHERE ri.rID = RE.rID
    ) t
    SET RE.rYieldCost = t.RecipeCost

When executed, I get the following error: "Unknown column 'RE.rID' in 'where clause'".

Any ideas?

@Adams : that should be an answer – Denys Séguret Apr 26 '12 at 19:04 — Denys Séguret, Apr 26 '12 at 19:04

score 1 · Accepted Answer · answered Apr 26 '12 at 19:05

1

Your inner derived query doesn't know anything about columns in the outer query. Try moving the WHERE into the UPDATE clause with something like WHERE t.rID = RE.rID.

answered Apr 26 '12 at 19:05

Marcus Adams

53,009
9
91
143

MySQL: "Unknown column in where clause" during Update Statement

1 Answers1

Linked