Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
  • Piet Souris
  • Himai Minh

Help on Hibernate query on Delete on duplicate rows

Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please read on� it is kinda lenghty...

First : I did this but the problem is:
I am finding that referencing rowid for select is OK� it is really getting what I want to be deleted but when it is executing the delete(uca), it is deleting based on accountId and revalidateFlag ONLY because rowId is not really a referencial entity as a physical column in the table.
For example: Data on UAC
B090000ZZZZZ 0 1 (RowId = 1)
B090000ZZZZZ 0 1 (RowId = 2)
B090000ZZZZZ 0 2 (RowId = 3)

During the SELECT process it is getting only one row:
B090000ZZZZZ 0 1 (RowId = 2)

But when it comes to deletion; it is deleting
B090000ZZZZZ 0 1 (RowId = 1)
B090000ZZZZZ 0 1 (RowId = 2)

Query query = session.createQuery("from UECID_Change_Accounts as uca1 "+
"where uca1.rowId < (select max(uca2.rowId) "+
"from UECID_Change_Accounts as uca2 " +
"where uca1.accountId = uca2.accountId "+
"and uca1.revalidateFlag = uca2.revalidateFlag)");

List ucaDelete = query.list();

Iterator it = ucaDelete.iterator();
while (it.hasNext()) {

UECID_Change_Accounts uca = (UECID_Change_Accounts);;




So I did the explicit DELETION using " dynamic Native SQL"
but am getting : Unsupported OperationException; detailMessage"Update queries only supported through HQL"
java.lang.UnsupportedOperationException: Update queries only supported through HQL

valuechar[41] (id=102)

String sql1="DELETE UECID_Change_Accounts uca1 " +
"where uca1.rowid < " +
" (SELECT max(rowid)" +
" from UECID_Change_Accounts uca2" +
" where uca1.accountId = uca2.accountId" +
" and uca1.revalidateFlag = uca2.revalidateFlag)";

Query q = session.createSQLQuery(sql1);
Int status = q.executeUpdate();

Ranch Hand
Posts: 121
IntelliJ IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How's the mapping of the UECID_Change_Accounts entity?
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This seems to me like an odd question. You say you are retrieving the object in the first instance from a select statement. Now if you are retrieving a full object, that object should have an ID (otherwise known as a primary key). So if you are to delete that from the table, then only the one row should be deleted.

I would question why you have a table which does not have a primary key and seriously consider the use of a surrogate key if you don't currently have a primary key.

Could you please post the table DDL and the associated annotated object (or hibernate mapping file if that is what you are using)?


Matt Gaunt
I would challenge you to a battle of wits, but I see you are unarmed - shakespear. Unarmed tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
    Bookmark Topic Watch Topic
  • New Topic