MySql update fields based on other fields

Question

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

Answer 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