jeudi 16 septembre 2021

mysql update subquery set

I couldn't find and couldn't produce a solution to this problem with SQL Mysql. I want to update two columns of a table, and the origin of these values ​​are from another table, having to come randomly. Here's a tentative example:

update table1 a1,
(select col1, col2 
 from table2  
 ORDER BY RAND() limit 1) a2
set a1.col1 = a2.col1, a1.col2 = a2.col2
where a1.col3 is not null;

From this form, the same value from table2 is always coming.

table1         |  table2
id  col1 col2  |  id col1 col2
1   aaa  bbb   |  1  xxx  yyy
2   ccc  ddd   |  2  www  ttt 
               |  3  uuu  vvv

I want the values ​​(col1, col2) from table 2 to be defined in table1 randomly (col1 and col2).

Without Limit 1, it is also being updated with the same record. As if there were 1 record in table2.

That is, for each line of the update, a subquery is made in the other table bringing a record randomly.




Aucun commentaire:

Enregistrer un commentaire