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
– Walter_Ritzel, Commented Apr 26, 2016 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
– J Greene, Commented Apr 26, 2016 at 13:53

Gordon Linoff · Accepted Answer · 2016-04-26 13:57:10Z

1

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.

answered Apr 26, 2016 at 13:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related