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();