Hi
could anybody give me appropriate code of HQL or CRITERIA for the following sql code :

UPDATE POLICY
SET EXPIREDATE=STARTDATE-1
WHERE P_CODE=(
SELECT MAX(P_NO)
FROM POLICY
WHERE P_CODE='&PCode'
)
-------------------------------------------
Here is table structure:

P_NO START_DATE EXPIRE_DATE PCODE
---------- ------------- -------------- ----------
5 23-APR-09 31-JUL-10 C001
6 01-AUG-09 08-NOV-09 C002
7 09-NOV-09 05-AUG-12 C003
8 09-NOV-09 05-AUG-12 C002
9 01-AUG-10 06-JUN-11 C001

Initially load the record you wanted to update and type cast to its corresponding entity. Then using setter method set the date and save the object.

how to write can u tell me plz

I am assuming your Policy entity named as Policy.

List<Policy> entityList = session.createQuery("load_latest_policy","select po from Policy po where po.policyCode=:policyCode order by po.policyNumber desc").setString("policyCode",value).list();

Above statement gives you records having same policy code.

now,
Calendar instance = Calendar.getInstance();
instance.add(Calendar.DATE, -1);
Date yesterday = instance.getTime();

for(Policy entity : entityList )
{
entity.setExpiryDate(yesterday);
session.saveOrUpdate(entity);
session.refresh(entity);
break;
}

Finally Which one I have to print...