MySQL Update Row from Query

Question

I've got a complex (For me!) query that basically is grabbing the data from 2 tables then grouping them togeher (This bit works 100% as a SELECT query)

But when I now need to update the database in another table, it won't work.

I've got this:

UPDATE 
    exp_channel_data data,
    (
        SELECT 
            posts.cat_id, 
            posts.entry_id, 
            cats.cat_name, 
            cats.cat_id,
            GROUP_CONCAT('{"',cats.cat_name, '"}:{"',cats.cat_name,'"}') as category_tag
        FROM 
            exp_category_posts posts, 
            exp_categories cats
        WHERE 
            cats.cat_id = posts.cat_id
        GROUP BY 
            posts.entry_id
    ) category
SET
    data.field_id_178 = category.category_tag
WHERE 
     data.entry_id = category.entry_id;

But I'm getting this error:

Duplicate column name 'cat_id'

I think its because i'm trying to make the connection between the two tables, BUT it's not linking.

Like I said, the SELECT query works on it's own, but when put in to the UPDATE - It just throws this error.

:(

Answer 1

You are very close to what you need:

UPDATE exp_channel_data data JOIN
       (SELECT posts.entry_id,
               GROUP_CONCAT('{"',cats.cat_name, '"}:{"',cats.cat_name,'"}') as category_tag
        FROM exp_category_posts posts JOIN
             exp_categories cats
             ON cats.cat_id = posts.cat_id
        GROUP BY posts.entry_id
       ) category
       ON data.entry_id = category.entry_id
    SET data.field_id_178 = category.category_tag;

The important part was removing cats.cat_id from the subquery. You had two columns with that name, confusing MySQL.

I also fixed the query to use proper, explicit JOIN syntax.