Code below could perform better, esp. if table2 and table3 don't have an index that directly supports the lookup/comparison being done.

Edit: And performance could be worse with this code ... can't be sure ahead of time without seeing table DDL, query plans, etc.. Although I wouldn't expect it to be much worse if it is.

UPDATE t1

SET col1 = 'aa'

FROM table1 t1

LEFT OUTER JOIN (

SELECT col3, MAX(col2) AS col2_max

FROM table2

GROUP BY col3

) AS t2 ON t1.col4 = t2.col3 AND t1.col1 < t2.col2_max

LEFT OUTER JOIN (

SELECT col3, MAX(col1) AS col1_max

FROM table3

GROUP BY col3

) AS t3 ON t2.col3 IS NULL AND t1.col4 = t3.col3 AND t1.col1 < t3.col1_max

WHERE t2.col3 IS NOT NULL

OR t3.col3 IS NOT NULL