mysql ，从选择结果更新

Question

我正在尝试执行类似的操作:

              UPDATE page_results
          SET cache_valid=0
          WHERE link_id IN (SELECT  DISTINCT  l.link_id
          FROM link_results AS l
          INNER JOIN page_results AS p ON p.link_id=p.link_id 
          WHERE has_no_robots=0 AND cache_valid=1 AND cache_to_expire=1 AND status_code!='404' AND  href!='' AND anchor_match!='' AND nofollow=0)

基本上，我想更新从派生表中获取的所有结果..

我的另一个解决方案是:

              UPDATE (SELECT  DISTINCT  l.link_id
          FROM link_results AS l
          INNER JOIN page_results AS p ON p.link_id=p.link_id 
          WHERE has_no_robots=0 AND cache_valid=1 AND cache_to_expire=1 AND status_code!='404' AND  href!='' AND anchor_match!='' AND nofollow=0) 
          SET cache_valid=0

但这也不起作用..有办法实现我的目标吗？

Answer 1

以下是执行此操作的正确语法:

UPDATE page_results t1
INNER JOIN link_results AS l ON t1.link_id = l.link_id 
SET t1.cache_valid = 0
WHERE t1.has_no_robots   = 0 
  AND t1.cache_valid     = 1 
  AND t1.cache_to_expire = 1 
  AND t1.status_code    != '404' 
  AND t1.href!           = '' 
  AND t1.anchor_match   != '' 
  AND t1.nofollow        = 0;