Need to rewrite query to use max function in a where clause

Question

Query being used at this time:

update metrics.time_created 
set cumu_count = (
    select count(*) 
    from perf_stats.time 
    where date(insert_datetime)='2015-12-18'
) 
where id=max(id);

I get an "invalid use of group function" here -- how can I rewrite this to keep the same logic? I have to update the most recent row in the metrics.time_created table.

Possible duplicate: http://stackoverflow.com/questions/22538287/max-function-in-where-clause-mysql — devlin carnate, Dec 19 '15 at 03:25

zedfoxus · Accepted Answer · 2015-12-19T04:07:42.163

1

Try this:

update metrics.time_created a
set cumu_count = (
  select count(*) 
  from perf_stats.time 
  where date(insert_datetime)='2015-12-18'
) 
where exists (
  select 1
  from (select max(id) as maxid from metrics.time_created) t
  where maxid = a.id
);

Example demo: http://sqlfiddle.com/#!9/6bc3cd/1

EDIT:

Based on comment, here's the change

update metrics.time_created a

set cumu_count = 
(
  select count(*) 
  from perf_stats.time pt
  where exists
  (
    select 1
    from (select max(curr_date) as mcd from metrics.time_created) x
    where mcd = date(insert_datetime)
  )
) 

where exists 
(
  select 1
  from (select max(id) as maxid from metrics.time_created) t
  where maxid = a.id
);

Example demo: http://sqlfiddle.com/#!9/fcc91a/1

edited Dec 19 '15 at 04:07

answered Dec 19 '15 at 03:27

zedfoxus

35,121
5
64
63

I get "ERROR 1093 (HY000): You can't specify target table 'time_created' for update in FROM clause" – user3299633 Dec 19 '15 at 03:29
Let me create a quick SQLFiddle. Hang on – zedfoxus Dec 19 '15 at 03:34
Updated answer based on comment – zedfoxus Dec 19 '15 at 03:40
I was using '2015-12-18' as a place holder, but what I really need to do is grab max(curr_date) from metrics.time_created. Can you assist with that? – user3299633 Dec 19 '15 at 03:48
Let me see what I can do – zedfoxus Dec 19 '15 at 03:50
Updated answer again based on comment – zedfoxus Dec 19 '15 at 04:00
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/98396/discussion-between-user3299633-and-zedfoxus). – user3299633 Dec 19 '15 at 04:05

score 0 · Answer 2 · answered Dec 19 '15 at 03:37

You can use self join something like this :

update metrics.time_created as t1
inner join (
    select max(id) as id 
    from metrics.time_created
) as t2 on t1.id = t2.id
set cumu_count = (
  select count(*) 
  from perf_stats.time 
  where date(insert_datetime)='2015-12-18'
)

score 0 · Answer 3 · answered Dec 19 '15 at 03:42

If you are trying to update one row with the maximum id, then you can use order by and limit:

update metrics.time_created
    set cumu_count = (select count(*)
                      from perf_stats.time
                      where date(insert_datetime) = '2015-12-18'
                     )
    order by id desc
    limit 1;

Need to rewrite query to use max function in a where clause

3 Answers3