Marcus Marcus - 1 year ago 80
SQL Question

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?

Answer Source

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 = 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download