Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help on Hibernate query on Delete on duplicate rows

 
Elderico Mendoza
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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()) {
ndelete1+=1;

UECID_Change_Accounts uca = (UECID_Change_Accounts)it.next();;

session.delete(uca);
}

tx.commit();
session.flush();

----------------------------------------------;

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

count41
hash0
offset0
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();

tx.ommit();
 
Rodrigo Lopes
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How's the mapping of the UECID_Change_Accounts entity?
 
Matt Gaunt
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • 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)?

Regards

Matt Gaunt
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic