How to update the latest row in MySQL table

Question

The table #__virtuemart_order_histories stores the orders with their virtuemart_order_ids and every order has the records with their own virtuemart_order_history_ids. I need to update the latest record in the range of certain order. Now I have the code:

$order_id = 1028;
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$fields = array($db->quoteName('order_status_code') . " = " . $db->quote('U'));
$conditions = array($db->quoteName('virtuemart_order_id') . " = " . $order_id);
$query->update($db->quoteName('#__virtuemart_order_histories'))->set($fields)->where($conditions)->order($db->quoteName('virtuemart_order_history_id') . ' DESC');
$db->setQuery($query);
$db->execute($query);

But it doesn't work how I need — it updates ALL the records of certain order and not only the latest one. How should I specify the condition of getting only the latest record?

Answer 1

Use limit to run on first row found. Since you order it by virtuemart_order_history_id, it will be latest record.

Instead of

$db->setQuery($query);

use

$db->setQuery($query, 0, 1);

Second argument is $offset, third is $limit.

Answer 2

There are two ways which you can do database independent way (Joomla recommended).

A. Set limit on query (works with Joomla 3.x only)

$query->setLimit($limit, $offset);
e.g. $query->setLimit(1, 0);

B. Set limit while assigning query to db object (Works with Joomla 2.5 & 3.x)

$db->setQuery($query, $offset, $limit);
e.g. $db->setQuery($query, 0, 1);