MYSQL UPDATE SELECT - upgrade Column

Question

BD Wordpress - table: wp_term_relationships

object_id   |   term_taxonomy_id|   term_order
    1       |       23          |           0
    2       |       22          |           0
    3       |       23          |           0
    4       |       22          |           0
    5       |       23          |           0
    6       |       21          |           0

I need to count how many 'products' belong in every 'category' and insert at column 'term_order'

for example:

object_id   |   term_taxonomy_id|   term_order
    1       |       23          |           3
    2       |       22          |           2
    3       |       23          |           3
    4       |       22          |           2
    5       |       23          |           3
    6       |       21          |           1

I did something like this, but it is not updating the field 'term_order':

UPDATE `letras`.`wp_term_relationships`
SET `term_order` = '2'
WHERE `wp_term_relationships`.`object_id` = '5' ;

SELECT  object_id as id_objeto, 
        COUNT(  `object_id` ) quantidade
FROM wp_term_relationships
WHERE `object_id` =  `object_id` 
GROUP BY  `term_taxonomy_id` 

Answer 1

Sorry!

I excute in phpMyAdmin:

update t
set term_order = t2.cnt
from wp_term_relationships t
  join (
    select term_taxonomy_id, count(*) cnt
    from wp_term_relationships
    group by term_taxonomy_id
) t2 on t.term_taxonomy_id = t2.term_taxonomy_id;

Error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from wp_term_relationships t join ( select term_taxonomy_id, count(*) cn' at line 3

Answer 2

You can join the table to itself with a subquery:

update t
set term_order = t2.cnt
from wp_term_relationships t
  join (
    select term_taxonomy_id, count(*) cnt
    from wp_term_relationships
    group by term_taxonomy_id
) t2 on t.term_taxonomy_id = t2.term_taxonomy_id;

• SQL Fiddle Demo

---

Since you are using MySQL, this should work:

update wp_term_relationships 
  join (
    select term_taxonomy_id, count(*) cnt
    from wp_term_relationships
    group by term_taxonomy_id
) t2 on wp_term_relationships.term_taxonomy_id = t2.term_taxonomy_id
set wp_term_relationships.term_order = t2.cnt;

• Updated Fiddle