CODEWITHSUNDEEP
mysql
user986959
user986959

Reputation: 630

MYSQL Update invalid use of group function

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.

Upvotes: 0

Views: 1102

Answers (1)

evenro
evenro

Reputation: 2646

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.

Upvotes: 1

Related Questions