mysql - 更新来自 SELECT 的数据

Question

从表 1 中选择:

SELECT a.region,a.host 
FROM ftpup a
 , ( SELECT info,type,timestamp, max(date) as time FROM ftpup GROUP BY type ) b 
WHERE a.type = b.type AND a.date = b.time AND a.status <> '0' ORDER BY a.`date` DESC

表 1 的结果:

region --- host
SB --- H1
BS --- H2
PT --- H2

表2:

hostname --- region --- status
H1 --- SB --- 1
H2 --- PT --- 0
H2 --- BS --- 1

如何在状态字段的表中UPDATE TABLE2并将其设置为2，其中第一个选择的hostname = host(RESULT FROM TABLE1)和region = 第一个选择的区域(表 1 的结果)？

Answer 1

就这么简单。只需像下面这样做:

UPDATE table2
INNER JOIN (your inner select) AS table1
ON (table2.hostname = table1.host AND table2.region = table1.region)
SET table2.status=2