sqlsql-updatenetezzamultiple-join-rows

How to prevent SQL multiple-join-rows error and apply values for multiple rows?

ERROR [HY000] ERROR: Update canceled: attempt to update a target row with values from multiple join rows

Is there a way to avoid this error and update each row that contains E, despite having multiple join rows?

UPDATE V
SET 
V.S = M.S,
V.T = M.T,
V.U = M.U
FROM MAP M
WHERE AND V.E = M.E;
Solution

  • You need to fetch one value per E. I would be tempted to use row_number():

    UPDATE V
    SET V.S = M.S,
        V.T = M.T,
        V.U = M.U
    FROM (SELECT M.*, ROW_NUMBER() OVER (PARTITION BY E ORDER BY E) as seqnum
          FROM MAP M
         ) M
    WHERE V.E = M.E AND seqnum = 1;

    However, you may have other logic for selecting the particular values from MAP that you want to use for the update.