0

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
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
André Ferreira
  • 78
  • 1
  • 7
  • 4
    This is what primary keys are for. – Chris Albert May 26 '22 at 13:45
  • Sorry, I forgot to say that there is no primary key in this table. I will update the question. – André Ferreira May 26 '22 at 13:48
  • 3
    what dbms are you using? – TZHX May 26 '22 at 13:48
  • 1
    Depending on the RDBMS, you could do something like [update the top 1 record from a select](https://stackoverflow.com/questions/20539095/update-top-1-record-in-table-sql-server). – David May 26 '22 at 13:50
  • I am using MariaDB, connected with MySQL Workbench. – André Ferreira May 26 '22 at 13:50
  • then I'd expect `LIMIT` to work as it does in SELECTs. – TZHX May 26 '22 at 13:51
  • 2
    The fact you are dealing with this is due to poor database design. If your rows ought to be unique, but aren't, your database should have ensured that rows are truly unique. Whether that's by adding an IDENTITY column that adds a unique value to every row, or by the use of a unique index that would prevent non-unique rows from making their way into the table. – SchmitzIT May 26 '22 at 14:06

1 Answers1

2

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

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26