Oracle SQL - Update Duplicate with a max value

Question

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.

Answer 1

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