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