MySQL update join with limit

join, mysql, phpmyadmin, sql

Solution

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

Problem

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.

Original source