faraa - 1 month ago 9

SQL Question

I want to update column c5 (to 1) of each grouped date base on column c1,c2,c3 where c3 is maximum in same c1,c2 group.I am working with hql on oracle db

`c1 | c2 | c3 | c4 | c5 | c5 after update`

2000 | a | 01 | x | 0 | 0

2000 | a | 01 | y | 0 | 0

2000 | a | 01 | z | 0 | 0

2000 | a | 02 | z | 0 | 1

2000 | a | 02 | x | 0 | 1

...........................

2000 | b | 01 | x | 0 | 0

2000 | b | 01 | y | 0 | 0

2000 | b | 01 | z | 0 | 0

2000 | b | 02 | z | 0 | 1

..........................

..........................

2001 | a | 01 | x | 0 | 0

2001 | a | 01 | y | 0 | 0

2001 | a | 01 | z | 0 | 0

2001 | a | 02 | z | 0 | 0

2001 | a | 02 | x | 0 | 0

2001 | a | 02 | y | 0 | 0

2001 | a | 02 | w | 0 | 0

2001 | a | 03 | y | 0 | 1

2001 | a | 03 | w | 0 | 1

...........................

2001 | b | 01 | x | 0 | 0

2001 | b | 01 | y | 0 | 0

2001 | b | 02 | x | 0 | 1

2001 | b | 02 | z | 0 | 1

Answer Source

I am not familiar with hql, but a quick Google search shows it's "similar" to SQL but it is object oriented. In your problem, you must update a standard, relational Oracle table, so it's not clear why you need or want to use hql.

If you have access to the Oracle database in a way that can use simple SQL statements, you can execute the update with the MERGE statement, like this: (assuming `td`

is the name of your table)

```
merge into td
using ( select c1, c2, max(c3) as max_c3
from td
group by c1, c2
) x
on (td.c1 = x.c1 and td.c2 = x.c2 and td.c3 = max_c3)
when matched then update
set c5 = 1
;
```