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.

CodePudding user response：

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