Skip to content
Advertisement

How to update only one of duplicated rows in SQL without primary keys?

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:

Update query:

Query:

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

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement