CODEWITHSUNDEEP
mysql
Dmitry Makovetskiyd
Dmitry Makovetskiyd

Reputation: 7053

mysql , updating from select results

I am trying to execute something like that:

              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)

Basically, I want to update all the results , that are taken from the derived table..

The other solution that I had was this:

              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

But that didnt work either.. Is there a way to acheive my goal?

Upvotes: 0

Views: 185

Answers (2)

Omesh
Omesh

Reputation: 29091

This is because of wrong condition p.link_id=p.link_id on join clause, try this query:

  UPDATE page_results p
         INNER JOIN link_results l
             ON p.link_id = l.link_id
  SET   p.cache_valid = 0
  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;

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Here is the right syntax to do this:

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;

Upvotes: 1

Related Questions