MySql基于其他字段更新字段 [英] MySql update fields based on other fields

问题描述

我试图更新所有WordPress文章的自定义元数据，其中另一个自定义元字段等于1。

 meta_id | post_id | meta_key | meta_value 
 --------------------------------------------- --- 
 0001 | 1234 | _p_free | 1 
 0002 | 1234 | new_free | null 
 0003 | 2345 | _p_free | 1 
 0004 | 2345 | new_free | null 
 0005 | 9876 | _p_free | 0 
 0006 | 9876 | new_free | null 

所以，我可以看到，我想做的是运行一个查询，找到一个meta_key = _p_free和meta_value = 1，然后用相同的post_id更新new_free也等于1.

感谢

解决方案

您需要使用多表 UPDATE 语法：

  UPDATE tbl AS t1 JOIN tbl AS t2 USING（post_id）
 SET t1.meta_value = 1 
 WHERE t2.meta_key ='_p_free'AND t2.meta_value = 1 
  

I'm trying to update the custom meta for all WordPress posts where another custom meta field equals 1.

My table looks like thus

meta_id  |  post_id  |  meta_key  |  meta_value
------------------------------------------------
0001     |   1234    |   _p_free  |    1
0002     |   1234    |   new_free |    null
0003     |   2345    |   _p_free  |    1
0004     |   2345    |   new_free |    null
0005     |   9876    |   _p_free  |    0
0006     |   9876    |   new_free |    null

So, as you can see, what I'd like to do is run a query that finds all posts with a meta_key = _p_free and a meta_value = 1 and then update the new_free with the same post_id to also equal 1.

Thanks

解决方案

You need to use the multiple-table UPDATE syntax with a self-join:

UPDATE tbl AS t1 JOIN tbl AS t2 USING (post_id)
SET t1.meta_value = 1
WHERE t2.meta_key = '_p_free' AND t2.meta_value = 1

这篇关于MySql基于其他字段更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！