Dima Kuzmin Dima Kuzmin - 1 month ago 10
SQL Question

UPDATE on data from the SELECT

SELECT FROM TABLE 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


RESULT FROM TABLE1:



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


TABLE2:


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



How do
UPDATE TABLE2
in the table for the status field and set it to 2, where
hostname = host
of the first select (RESULT FROM TABLE1) and
region = region
of the first select (RESULT FROM TABLE1)?

Answer

So easy. Just do it like below:

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