CODEWITHSUNDEEP
mysqlsql
mctjl_1997
mctjl_1997

Reputation: 163

Update where value is max from same table

I'd like to do this.

update cart set productname='hey' where cartid=(select max(cartid) from cart)

However, the sql shows the error where the table 'cart' is defined twice. How do i get around this?

Upvotes: 0

Views: 2433

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

In MySQL, you can use update with limit:

update cart
    set productname = 'hey'
    order by id desc
    limit 1;

You can review the documentation here.

If you want to calculate the value, you can use a join:

update cart c join
       (select max(id) as maxid from card) cc
       on c.id = cc.maxid
    set productname = 'hey';

This would be particular useful if multiple rows could have the maximum id and you wanted all to be updated.

Upvotes: 2

Related Questions