faraa faraa - 3 months ago 15
SQL Question

update conditionally base on maximum of a column in a special group hql

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
;