How to compose Update according to Select data by condition from another table?

Question

MySQL

There is a table 1 from which we get a selection:

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

Sample result:
region --- host
SB --- H1
BS --- H2
PT --- H2
There is table 2, with the structure:
host --- region --- status
H1 --- SB --- 1
H2 - -- PT --- 0
H2 --- BS --- 1
How to compose update for table 2 and set status='2' where host = host from first select (result from table 1) and region=region from first select ( result from table 1)?

Answer 1

Yes, I already blinded a little earlier. You have the same logic.

UPDATE table2
INNER JOIN (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) AS table1
ON (table2.hostname = table1.host AND table2.region = table1.region)
SET table2.status=2

Answer 2

I'm not sure, but try this (try on test data):

UPDATE my_tab2 as t2 
  JOIN (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' ) AS t1 ON t1.host = t2.host and t1.region = t2.host
   SET t2.status = '2'