I have a table that have the following columns:

I want to update the st_id value on the last row of the table to 2011. How can I update only one of the duplicated values in this table, when there is no primary key in it?

Result expected:

CodePudding user response：

Schema and insert statement:

 create table testTable(st_id int, sbj_id int, description varchar(50), scr float, sbm_dt datetime);
 insert into testTable values(2001, 10, 'Q2',   7.4,    '2021-05-03 17:03:32');
 insert into testTable values(2001, 10, 'Q1',   8.0,    '2021-04-03 18:07:35');
 insert into testTable values(2011, 10, 'Q1',   5.0,    '2021-04-03 19:07:35');
 insert into testTable values(2001, 10, 'Q2',   7.4,    '2021-05-03 17:03:32');

Update query:

 update testTable set st_id=2011 where st_id=2001  order by sbm_dt desc limit 1

Query:

 select  * from testTable

Output:

db<>fiddle here