MySQL更新加入限制 [英] MySQL update join with limit
问题描述
我想知道如何使用另一个表中的值来更新一个表,诀窍是我需要设置一个限制,因为我有成千上万的行要更新,而PHPmyadmin无法处理该负载. (我没有直接访问服务器的权限)
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 )
我的表结构如下
wp_postmeta
meta_id,
post_id,
meta_key,
meta_value
meta_id,
post_id,
meta_key,
meta_value
wp_map
oldmap, newmap
我需要做的是联接wp_postmeta.meta_value
和wp_map.oldmap
上的两个表,并用wp_map.newmap
更新wp_postmeta.meta_value
.
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
.
这是我当前的查询,但是我需要在查询中添加100的LIMIT
,因为我将查询分为较小的块,以便PHPMyAdmin可以处理.
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.
推荐答案
您可以通过这种方式尝试
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;
这里是 SQLFiddle 演示
Here is SQLFiddle demo
这篇关于MySQL更新加入限制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!