MySQL update join with limit

问题:

I would like to know how to update a table, with values from another table, by the trick is I need to set a limit because I have thousands of rows to update, and PHPmyadmin can't handle that load. ( I dont have direct access to the server )

My table structure looks like this

wp_postmeta

meta_id, post_id, meta_key, meta_value

wp_map

oldmap, newmap

What I need to do is join the two tables on wp_postmeta.meta_value and wp_map.oldmap, and update the wp_postmeta.meta_value with wp_map.newmap.

Here is my current query, but I need to add a LIMIT of 100 to the query, as I'm splitting the query up into smaller chunks so PHPMyAdmin can process.

UPDATE wp_postmeta
INNER JOIN wp_map
ON wp_map.oldmap = wp_postmeta.meta_value
SET wp_postmeta.meta_value = wp_map.newmap;

I read about creating a subquery, but couldn't find any relevant examples, so if someone could steer me in the right direction or provide a working example it would be greatly appreciated.

回答1:

You can try it this way

UPDATE wp_postmeta t JOIN
(
    SELECT p.meta_id, m.newmap
      FROM wp_postmeta p JOIN wp_map m
        ON p.meta_value = m.oldmap
     ORDER BY p.meta_id
     LIMIT 100
) s
   ON t.meta_id = s.meta_id
  SET t.meta_value = s.newmap;

Here is SQLFiddle demo

回答2:

sqlFiddle in this example I am only doing 5 rows, you can change the limit to 0,100 if you'd like

UPDATE wp_postmeta,
      (SELECT oldmap,newmap
       FROM wp_map 
       ORDER BY oldmap,newmap limit 0,5)as wp_map
SET wp_postmeta.meta_value = wp_map.newmap
WHERE wp_map.oldmap = wp_postmeta.meta_value

回答3:

It's rather unconventional , but it should help you in a pinch

SET @tmp_c = 0;
UPDATE
    table1
INNER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table2.id = table3.id
SET
    table1.remove_date = NOW() - INTERVAL 5 MONTH
WHERE
    table1.active = 1
AND
    (IF (table1.active = 1, @tmp_c := @tmp_c + 1 , 0 )) //This is to only increment the counter if the whole where matches
AND
    @tmp_c <= 15 //Start ignoring row items as soon as it exceeds the limit amount