Take the 2-minute tour ×

Is there a way to update a value based on the result of left joins and group by?

I want to be able to update category with current jobs to visible and I tried below but it doesn't work

UPDATE category c 
            LEFT JOIN portfolio_job_category pjc
            ON pjc.category_id = c.id
            LEFT JOIN job j
            ON j.id = pjc.job_id
            SET c.visible = 0
            WHERE (c.visible = 0 
            OR c.visible IS NULL)
            AND j.end_date > CURRENT_DATE
            GROUP BY cat_id
share|improve this question
group by is not needed there –  John Woo Jan 22 '13 at 4:25
add comment

1 Answer

up vote 0 down vote accepted

GROUP BY is not part of the grammar for UPDATE statements (link). GROUP BY as part of an update doesn't make a lot of sense, because there are no aggregate functions involved. If your aim is just to update all categories that are associated with jobs that haven't ended yet, you should be able to accomplish this with just the joins you have.

Also, the c.visible = 0 portion of the disjunction in the WHERE clause seems redundant. No need to set visible to 0 if it's already 0.

share|improve this answer
add comment

Your Answer


