CODEWITHSUNDEEP
sql
Valikahn
Valikahn

Reputation: 23

SQL UPDATE statement throws error "Invalid use of group function"

The SQL UPDATE statement keeps throwing an error

Invalid use of group function

Code:

UPDATE client
SET car_id = '6', 
    car_name = 'Wedding Limousine', 
    rentdate = '2019-07-27', 
    days = '2', 
    hire_cost_total = '41.99'
WHERE client_id = MAX(client_id);

I think the WHERE client_id = MAX(client_id) is incorrect but I am not sure how to resolve this error.

I appreciate the assistance.

Upvotes: 0

Views: 51

Answers (3)

forpas
forpas

Reputation: 164099

If you want to update the row with the maximun client_id:

UPDATE client
SET car_id = '6', car_name = 'Wedding Limousine', rentdate = '2019-07-27', 
days = '2', hire_cost_total = '41.99'    
WHERE client_id = (
  SELECT t.client_id FROM (SELECT MAX(client_id) as client_id FROM client) as t
);

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You should obatin the max client_id using a subquery 

UPDATE client
SET car_id = '6'
    , car_name = 'Wedding Limousine'
    , rentdate = '2019-07-27'
    , days = '2'
    , hire_cost_total = '41.99'
WHERE client_id = (
  select MAX(client_id)
  from client 
)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270021

In MySQL you can use order by and limit:

UPDATE client
    SET car_id = '6',
        car_name = 'Wedding Limousine',
        rentdate = '2019-07-27', 
        days = '2',
        hire_cost_total = '41.99'
    ORDER BY client_id DESC
    LIMIT 1;

Upvotes: 1

Related Questions