SQL statement: how can I pick a value only once from a table to update another table?

Question

I wish to update table1 with any distinct value of table2 matching the same code, no matter which one or the order (value from table2 can't be picked more than once)

+-------------------+      +--------------+
|      table1       |      |    table2    |
+-------------------+      +--------------+
| id | code | value |      | code | value |
+----+------+-------+      +------+-------+
| 1  |  A   |       | <--  |  A   |  v1   |
| 2  |  A   |       |      |  B   |  v2   |
| 3  |  B   |       |      |  A   |  v3   |
+----+------+-------+      |  A   |  v5   |
                           |  A   |  v6   |
                           +------+-------+
+-------------------+                      
|      table1       |                      
+-------------------+                      
| id | code | value |
+----+------+-------+
| 1  |  A   |  v6   |
| 2  |  A   |  v3   |
| 3  |  B   |  v2   |
+----+------+-------+

How can I write the SQL update statement ? (MySQL 5.7)

Why haven't you picked v1 ? Do you have access to latest MySQL version (8.0.2 and above) ? — Madhur Bhaiya, Nov 09 '18 at 16:34
Can you provide a DB Fiddle or SQL fiddle. I can try to optimize the posted solution further. — Madhur Bhaiya, Nov 09 '18 at 17:04

Madhur Bhaiya · Answer 1 · 2018-11-10T04:47:18.027

1

This requires Row_Number() Window function's magic! Unfortunately, your MySQL version is 5.7; so a more verbose solution using User-defined variables follows:

UPDATE 
table1 AS t1 

JOIN 

(SELECT 
   dt1.id, 
   IF(@cd1 = dt1.code, @rn1 := @rn1 + 1, 1) AS row_num1, 
   @cd1 := dt1.code AS code 
 FROM (SELECT id, code FROM table1 ORDER BY code, id) AS dt1
 CROSS JOIN (SELECT @rn1 := 0, @cd1 := '') AS init1
) AS t2 
  ON t2.id = t1.id 

JOIN 

(SELECT 
   IF(@cd2 = dt2.code, @rn2 := @rn2 + 1, 1) AS row_num2, 
   @cd2 := dt2.code AS code, 
   dt2.value 
 FROM (SELECT code, value FROM table2 ORDER BY code) AS dt2
 CROSS JOIN (SELECT @rn2 := 0, @cd2 := '') AS init2
) AS t3 
  ON t3.row_num2 = t2.row_num1 AND 
     t3.code = t2.code 

SET t1.value = t3.value

You can check the explanation of a similar technique in this answer.

edited Nov 10 '18 at 04:47

answered Nov 09 '18 at 16:50

Madhur Bhaiya

28,155
10
49
57

This doesn't work, not because of the extra comma in `@cd1 := dt1.code AS code, FROM (SELECT id` but because it repeats value of table2. How about sorting by table2.code and using a variable to skip used value ? – guigoz Nov 09 '18 at 20:52
@guigoz I have fixed the syntax error. Can you please try to provide a https://www.db-fiddle.com/ This will help me in playing with the data and optimize as well as fix the logic further. – Madhur Bhaiya Nov 10 '18 at 04:48

guigoz · Accepted Answer · 2018-11-10T07:46:49.987

1

The statement provided by Madhur Bhaiya does work if

@rn* are initialized to 1 instead of 0 (otherwise row_num* are numbered 1 twice)
the SELECT from table2 is DISTINCT (because pairs of (code,value) are repeated in table2)

The statement should be

UPDATE 
table1 AS t1 

JOIN 

(SELECT 
   dt1.id, 
   IF(@cd1 = dt1.code, @rn1 := @rn1 + 1, 1) AS row_num1, 
   @cd1 := dt1.code AS code,
 FROM (SELECT id, code FROM table1 ORDER BY code, id) AS dt1
 CROSS JOIN (SELECT @rn1 := 1, @cd1 := '') AS init1
) AS t2 
  ON t2.id = t1.id 

JOIN 

(SELECT 
   IF(@cd2 = dt2.code, @rn2 := @rn2 + 1, 1) AS row_num2, 
   @cd2 := dt2.code AS code, 
   dt2.value 
 FROM (SELECT DISTINCT code, value FROM table2 ORDER BY code) AS dt2
 CROSS JOIN (SELECT @rn2 := 1, @cd2 := '') AS init2
) AS t3 
  ON t3.row_num2 = t2.row_num1 AND 
     t3.code = t2.code 

SET t1.value = t3.value

edited Nov 10 '18 at 07:46

answered Nov 10 '18 at 07:44

guigoz

674
4
21

Regarding first point, row number will initialized to 1 for different code values, and I believe that is what was needed. Perhaps though, without looking at complete data, and hence SQL / DB fiddle, it is hard to figure out your actual requirements. – Madhur Bhaiya Nov 10 '18 at 07:46
row_num is actually initialized to 1 for different code with `if(..., ..., 1)`, that is ok, but for next same code row_num is 1 again with `if(..., @rn:=@rn+1, ...)` if @rn is initialized to 0 – guigoz Nov 10 '18 at 10:59

Zaynul Abadin Tuhin · Answer 3 · 2018-11-09T16:40:24.610

0

use join ,As order does not matter so i think your sample output could be changed

    UPDATE table1 a 
    JOIN table2 b ON a.code= b.code 
    set a.value = b.value

edited Nov 09 '18 at 16:40

answered Nov 09 '18 at 16:39

Zaynul Abadin Tuhin

31,407
5
33
63

And how will this ensure that same value is not repeated ? – Madhur Bhaiya Nov 09 '18 at 16:40

Walter Rodriguez · Answer 4 · 2018-11-09T17:18:15.140

Using OVER functions should work:

Example queries:

select id,code,value,rank() over (partition by code order by id asc) rank_ 
from dbo.table1;

select code,value,dense_rank() over (partition by code order by code,value asc) rank_ from dbo.table2;

Update statment:

UPDATE t 
SET t1.value = t2.value 
FROM (select id,code,value,rank() over (partition by code order by id asc) rank_ from dbo.table1) t1
inner join ( select code,value,dense_rank() over (partition by code order by code,value asc) rank_ from dbo.table2 ) t2
on t1.code = t2.code and t1.rank_ = t2.rank_

SQL statement: how can I pick a value only once from a table to update another table?

4 Answers4