CODEWITHSUNDEEP
sqloracle-databaseplsqlduplicates
paolo ricardos
paolo ricardos

Reputation: 65

Oracle SQL - Update Duplicate with a max value

I'm looking for an UPDATE statement where it will update a row that have duplicated timestamp and update only the row that have a MAX value at the duration column, I can utilize in Oracle SQL or PL/SQL.

Here is an example TABLE1 to work with:

ID TIME DURARTION VALID
1 12:30 6 -
2 12:35 2 -
3 12:35 5 -
4 12:35 9 -

Result expected:
ID TIME DURARTION VALID
1 12:30 6 TRUE
2 12:35 2 FALSE
3 12:35 5 FALSE
4 12:35 9 TRUE

I tried this query:

update TABLE1 set VALID = 'TRUE' where id not in (select max(id) from TABLE1 group by TIME)

but it update only the row with the max ID, I couldn't figure figure out how to update the row with the max DURATION.

Thanks.

Upvotes: 0

Views: 180

Answers (1)

VBoka
VBoka

Reputation: 9083

You can use case clause to update value with TRUE or FALSE.

update TABLE1
set tru_false = case when (TIME, duration) in (select time, max(duration) from TABLE1 group by time)
                then 'TRUE'
                else 'FALSE'
                END

Here is a small demo that I hope it will help

Upvotes: 2

Related Questions