Update multiple columns in MySql with results from a SELECT statement

Question

Background: I have an orders table that contains address columns. I would like to update these with randomly picked addresses taken from a temporary table

Both tables contain address, address1, city and postcode columns

I was thinking the query would be something like:

UPDATE orders (address, address1, city, postcode)
VALUE
(SELECT address, address1, city, postcode
FROM addresses
ORDER BY RAND()
LIMIT 1)

Edit: Note that it needs update all rows with different values.

Use multiple-table UPDATE syntax.

Akina
– Akina

2023-02-03 12:37:35 +00:00
Commented Feb 3, 2023 at 12:37 — Akina
– Akina, Commented Feb 3, 2023 at 12:37

Akina · Accepted Answer · 2023-02-03 12:39:55Z

3

UPDATE orders 
JOIN ( SELECT address, address1, city, postcode
       FROM addresses
       ORDER BY RAND()
       LIMIT 1 
       ) AS newdata
SET orders.address = newdata.address,
    orders.address1 = newdata.address1,
    orders.city = newdata.city,
    orders.postcode = newdata.postcode
WHERE orders.id = 72;

answered Feb 3, 2023 at 12:39

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barnaby Over a year ago

Your answer worked perfectly for one row, however when I remove the where clause at the end all rows are getting the same data. I need each row to be a different random selection from the addresses table.

Akina Over a year ago

@Barnaby I help to solve posted task only. If you have another task then create another question.

Collectives™ on Stack Overflow

Update multiple columns in MySql with results from a SELECT statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related