MySql update fields based on other fields

问题:

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

回答1:

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