使用SELECT语句的结果更新MySql中的多个列

z9smfwbn 于 2023-02-07 发布在 Mysql

关注(0)|答案(1)|浏览(146)

背景：我有一个包含地址列的订单表，我想用从临时表中随机选取的地址更新这些列
两个表都包含address、address1、city和postcode列
我想查询应该是这样的：

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

mysql

来源：https://stackoverflow.com/questions/75335538/update-multiple-columns-in-mysql-with-results-from-a-select-statement

1条答案

按热度按时间

yc0p9oo01#

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;

赞(0）回复(0）举报 2023-02-07

我来回答

使用SELECT语句的结果更新MySql中的多个列

1条答案

相关问题

热门标签

最新问答