• Post Reply Bookmark Topic Watch Topic
  • New Topic

Help on Hibernate query on Delete on duplicate rows  RSS feed

 
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!