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

Question

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;

One alternative would be use a function like MIN or MAX, according to your logic. — Walter_Ritzel, Apr 26 '16 at 13:49
Another method is to use ROW_NUMBER() over (partition by .... order by .... ) This is assuming you know which of the multiple record rows you want to select. Then just update where ROW_NUMBER() = 1 — J Greene, Apr 26 '16 at 13:53

score 1 · Accepted Answer · answered Apr 26 '16 at 13:57

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.

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

1 Answers1