Using 'Group By' to Select the Row with the 'max(date)' and Updating it
update item_rate set rate_of_tax = 18 where id in (select id from (select id from item_rate where date_effective in (select max(date_effective) from item_rate group by item_name)) as t1) Explained : First get the maximum date_effective for each item_name. Get the item_rate row id for that date Place it into a temporary table t1 Select the ids from the temporary table Update the item_rate table, set the rates of tax for the selected row ids from the temporary table. Note : The temporary table is required because the table to be updated cannot be the same as the table being queried. item_rate Table ------------------------------------------------- | id | date_effective | rate_of_tax | item_name | ------------------------------------------------- | 1 | 01.Jan.2017 | 12.0 | item1 | | 2 | 15.Jan.2017 | 12.0 ...