Home > Software design >  How to update only one of duplicated rows in SQL without primary keys?
How to update only one of duplicated rows in SQL without primary keys?

Time:05-27

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

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:

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

  • Related