MYSQL Update invalid use of group function

Question

I want to update the current bonus points for some customer and I'm running the following query:

UPDATE bp_booking SET `current_bonuspoints`= 
(SELECT customers_bonus_points FROM customers WHERE customers_id='505') 
WHERE` `customers_id`=505 AND date_altered=max(date_altered);

The error I get is #1111 - Invalid use of group function. I had a look at THIS thread but I'm not sure how to apply the answer to my problem.

PHP:

while ( $booking_result = xtc_db_fetch_array($booking_customers_id) ) {
            foreach ($booking_result as $value) {
                $update_bp = xtc_db_query("
                SET @ned = (SELECT max(date_altered) FROM bp_booking WHERE customers_id='".$value."');
                UPDATE bp_booking SET `current_bonuspoints`= (SELECT customers_bonus_points FROM customers WHERE customers_id='".$value."')  WHERE `customers_id`='".$value."' AND date_altered=@ned;");
                } 
        }

I tried the code above but nothings happens.

evenro · Answer 1 · 2014-04-17T15:03:39.367

1

The max(date_altered) is invalid...

You either need to use a subselect ot select it, or to give it as an input.

(I can't rewrite the query for you since I don't know what your intent, but think of it that way: what is date_altered=max(date_altered)? is it only for the user? e.g.

where... date_altered = (select max(date _altered) from customers)

or is it for a specific user?

where... date_altered = (select max(date _altered) from customers where customers_id = 5)

The DBMS needs to know what it max's on ... and that's the reasoning behind the error.

This code works, and show that you can use subselect in an update:

create table a (a int);

insert into a values (1);
insert into a values (2);
insert into a values (3);

update a set a=4 where a = (select max(a) from a);

all commands execute successfully , and the world is happy :)

in MySQL, which I find really weird that it doesn't work as is, use:

update a  a1 join (select a, max(a) mm from a) a2 on a1.a = a2.a set a1.a = a2.mm

or something like it.

edited Apr 17 '14 at 15:03

answered Apr 17 '14 at 14:45

evenro

2,626
20
35

You cannot use subselect in Update, at least that's what phpmyadmin says – user986959 Apr 17 '14 at 14:46
I'm mainly an Oracle person, and I thought this is something that is required by ANSI... I'll test in on my MSSQL and be back in a few minutes... – evenro Apr 17 '14 at 14:49
It is a valid syntax, updating answer just to reflect what I did – evenro Apr 17 '14 at 14:52
So I see the error you're talking about, my only idea of fixing it is using a direct join instead of a subselect, try to build a working example, and update it. – evenro Apr 17 '14 at 14:58
Wrote something for MySQL - review it, and check that it works. notice that if you don't use a PK/FK relations, you'll have to enable "unsafe updates" for it to execute. – evenro Apr 17 '14 at 15:04

MYSQL Update invalid use of group function

1 Answers1