Update specific column based on other column value

I’m trying to update in Table 2 two columns L1, L2 based on the Lcolumn value in Table 1. If in Table 1 Lcolumn = "L1" then in Table 2 the L1 column has to be updated, otherwhise the L2 column.

Table 1

Table 2

In the example there are four Lcodes, for 50 – L1 and L2 and 51 – L1 and L2

When I run two individual queries for L1 and L2 ( fiddle 1 )

UPDATE table2 B
    LEFT JOIN table1 A
    ON( A.fnr = B.fnr AND A.gnr = B.gnr AND A.wnr = B.wnr AND A.Lcolumn = "L1" )
    SET 
        B.L1= A.Lcode 
    WHERE A.Lcode IS NOT NULL

(and the same for L2) the updates give the desired result: in Table 2 for wnr 50 and 51 both L1 and L2 are filled with the corresponding Lcodes.

Table 2 – desired result

When however I combine the two queries ( fiddle 2 )

UPDATE table2 B
  LEFT JOIN table1 A
  ON( A.fnr = B.fnr AND A.gnr = B.gnr AND A.wnr = B.wnr )
SET 
  B.L1= IF( A.Lcolumn = "L1" , A.Lcode , B.L1 ), 
  B.L2= IF( A.Lcolumn = "L2" , A.Lcode , B.L2 )
WHERE A.Lcode IS NOT NULL

I expected the same result, but in reality per wnr only one of the L-columns is updated, the other one stays NULL.

I can live with 2 queries, it’s more the WHY that’s bothering me because there are two results for every wnr from the JOIN ( fiddle 3 ), each with it’s own Lcolumn. So why is the query skipping rows (or resetting values?). And if – as a bonus – someone can solve the problem, that would be great.

>Solution :

Columns L1 and L2 are independent, source data lines for them are independent, so you must use independent source table copies:

UPDATE table2 t2
LEFT JOIN table1 t1L1 USING (fnr, gnr, wnr)
LEFT JOIN table1 t1L2 USING (fnr, gnr, wnr)
SET t2.L1 = COALESCE(t1L1.Lcode, t2.L1),
    t2.L2 = COALESCE(t1L2.Lcode, t2.L2)
WHERE t1L1.Lcolumn = 'L1'
  AND t1L2.Lcolumn = 'L2';

https://www.db-fiddle.com/f/diug6jeb1oabRZMus3MRko/2