dimanche 30 juillet 2017

MySQL: Copy Multiple Values from random row of another table

I have asked similar questions for Microsoft SQL Server and for PostGresql. Solutions which work there don’t work for MySQL.

I have two tables, stuff and nonsense. I would like to copy multiple values from a random row in nonsense to each row in stuff. Of course, there will be duplicates.

STUFF
+----+---------+-------------+--------+
| id | details | data        | more   |
+====+=========+=============+========+
| 1  | one     | (null)      | (null) |
+----+---------+-------------+--------+
| 2  | two     | (null)      | (null) |
+----+---------+-------------+--------+
| 3  | three   | (null)      | (null) |
+----+---------+-------------+--------+
| 4  | four    | (null)      | (null) |
+----+---------+-------------+--------+
| 5  | five    | (null)      | (null) |
+----+---------+-------------+--------+
| 6  | six     | (null)      | (null) |
+----+---------+-------------+--------+

NONSENSE
+----+---------+-------------+
| id | data    | more        |
+====+=========+=============+
| 1  | apple   | accordion   |
+----+---------+-------------+
| 2  | banana  | banjo       |
+----+---------+-------------+
| 3  | cherry  | cor anglais |
+----+---------+-------------+

I would like to be able to copy into the stuff table with something like:

UPDATE stuff SET data=?,more=?
FROM ?

If it were a single value, I could use a correlated subquery, but it won’t work properly for multiple values from the same row.

Newer PostGresql has the ability to copy into multiple columns from a correlated subquery. SQL Server the OUTER APPLY clause which allows a subquery in the FROM clause to be correlated. Neither approach works for MySQL.

How can I copy multiple values from random rows in another table?




Aucun commentaire:

Enregistrer un commentaire