I have a table that have the following columns:
st_id | sbj_id | desc | scr | sbm_dt |
---|---|---|---|---|
2001 | 10 | Q2 | 7.4 | 2021-05-03 17:03:32 |
2001 | 10 | Q1 | 8.0 | 2021-04-03 18:07:35 |
2011 | 10 | Q1 | 5.0 | 2021-04-03 19:07:35 |
2001 | 10 | Q2 | 7.4 | 2021-05-03 17:03:32 |
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:
st_id | sbj_id | desc | scr | sbm_dt |
---|---|---|---|---|
2001 | 10 | Q2 | 7.4 | 2021-05-03 17:03:32 |
2001 | 10 | Q1 | 8.0 | 2021-04-03 18:07:35 |
2011 | 10 | Q1 | 5.0 | 2021-04-03 19:07:35 |
2011 | 10 | Q2 | 7.4 | 2021-05-03 17:03:32 |
Advertisement
Answer
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:
st_id | sbj_id | description | scr | sbm_dt |
---|---|---|---|---|
2011 | 10 | Q2 | 7.4 | 2021-05-03 17:03:32 |
2001 | 10 | Q1 | 8 | 2021-04-03 18:07:35 |
2011 | 10 | Q1 | 5 | 2021-04-03 19:07:35 |
2001 | 10 | Q2 | 7.4 | 2021-05-03 17:03:32 |
db<>fiddle here