I still hate MySQL

Back in the MySQL saddle again. This time I'd like to update a table and it barks that I have an "invalid use of group function" errno 1111

    UPDATE
        HOME_WAREHOUSE HW
        INNER JOIN
            HOME_CHANGED HC
            ON HC.transaction_date = HW.transaction_date
            AND HC.home_id = HW.home_id
    SET
        HW.foo_count = count(DISTINCT HC.foo_id)
    WHERE
        HC.foo_change > 0

Sure looks fine to my TSQL eye. More work, less blog