CODEWITHSUNDEEP
mysql
user3299633
user3299633

Reputation: 3380

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

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.

Upvotes: 0

Views: 85

Answers (3)

zedfoxus
zedfoxus

Reputation: 37069

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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;

Upvotes: 0

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

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'
)

Upvotes: 0

Related Questions