CODEWITHSUNDEEP
mysqlsql-updateinner-join
Mr UD
Mr UD

Reputation: 1

ERROR 1111 (HY000): Invalid use of group function in MySQL update

update
  table1 a
  inner join table2 b on a.unique_id = b.unique_id
set
  a.menu_return = sum(
    case when b.value1 = 'mainmenu'
    and b.value2 = '0' then 1 else 0 end
  )
where
  a.time_stamp >= '2021-12-17 00:00:00'
  and a.time_stamp < '2021-12-18 00:00:00';

ERROR 1111 (HY000): Invalid use of group function

What cause this issue?

Upvotes: 0

Views: 100

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

I'm not really understand what you try to achieve by this query, but looks as you need to use join with pre-aggregated query like:

update  table1 a
    inner join (
      select 
        unique_id,  
        sum( case when value1 = 'mainmenu' and value2 = '0' then 1 else 0 end ) menu_return
        from table2 
        group by unique_id
    ) b on a.unique_id = b.unique_id
    set
      a.menu_return = b.menu_return
    where
      a.time_stamp >= '2021-12-17 00:00:00' and a.time_stamp < '2021-12-18 00:00:00';

You can play with MySQL online here

Upvotes: 1

Related Questions