error update with where clause in DB.executeUpdate compiere

Question

i want to update isproven to N in table m_requisition

where m_requisition_id in xx_reqverification = 'value from input'

and docstatus in xx_reqverification = 'VO'

StringBuffer s = new StringBuffer("UPDATE M_Requisition R SET")
    .append(" IsProven=").append("'N'")
    .append(" FROM XX_ReqVerification AS RV")
    .append(" WHERE RV.DocStatus='VO'")
    .append(" AND RV.XX_ReqVerification_id=")
    .append(veri.getXX_ReqVerification_ID())
    .append(" AND R.M_Requisition_id = RV.M_Requisition_id").append(";");
DB.executeUpdate(s.toString(), null);

but this code throw an error

DB.saveError: DBExecuteError - ERROR: syntax error at or near "where"

i am using postgresql for database

when i print s to console

UPDATE M_Requisition R SET IsProven='N' 
FROM XX_ReqVerification AS RV 
WHERE RV.DocStatus='VO' AND RV.XX_ReqVerification_id =1000040 
      AND R.M_Requisition_id = RV.M_Requisition_id;

i don't know what wrong with my code, please help me fix this.

Answer 1

While it's not an answer to your question specifically - see my comment/question regarding that above...

Updating the database directly like this would not be a recommended approach to customizing Adempiere. It could potentially break the consistency in the application as you would be bypassing the inbuilt mechanisms of the application such as WorkFlows, ModelValidators and perhaps Callouts. These mechanisms, along with the "Application Dictionary" exist to allow customization of the ERP application without risking inconsistencies.

If you used the inbuilt mechanisms to save an entity there would be no risk of breaking the application. Every "entity" in the application model extends a class called PO (Persistent Object) that has a save() method. Using this instead of a direct DB update would ensure all the rules defined in the Application Dictionary are followed as well as ensuring the functionalities required via the mechanisms mentioned above are run.

It should actually be an easier route with something like...

MRequisition req = new MRequistion(getCtx(), requisition_id, get_TrxName());
req.setDocStatus(DOCSTATUS_Voided);
req.setIsApproved(false);
req.save();

I can also recommend reading the following page from the wiki on extending Adempiere

One final point, there is often logic associated with a Document Status changes that it might be worth investigating too!