Problem while using select query as inner query for update query.

[Ganga Krishna Yenishetty <Ga...@infosys.com>]

Hi,
I am using select query as inner query in update query.
Getting following exception:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/infosys/qreuse/reuse/domain/conf/RoleMaster.xml.
--- The error occurred while executing update.
--- Check the                           UPDATE ROLEMASTER                           SET REPORTSTO = (SELECT ROLEID AS roleId FROM ROLEMASTER WHERE ROLENAME = ? AND GROUPID = ?),LASTMODIFIEDBY = ?                               WHERE REPORTSTO = (SELECT ROLEID AS roleId FROM ROLEMASTER WHERE ROLENAME = ? AND GROUPID = ?)                                          AND ROLEID NOT IN (SELECT ROLEID As roleIdArr FROM ROLEMASTER WHERE ROLENAME = ? AND GROUPID = ?)                    .
--- Check the SQL Statement (preparation failed).
--- Cause: java.sql.SQLException: Cannot modify table or view used in subquery.

My query is,
      <update id="updateReportsTo" parameterClass="com.infosys.qreuse.reuse.domain.RoleMaster">
            UPDATE ROLEMASTER
            SET REPORTSTO = (SELECT ROLEID AS roleId FROM ROLEMASTER WHERE ROLENAME = #roleName# AND GROUPID = #groupId#),LASTMODIFIEDBY = #lastModifiedBy#
            WHERE REPORTSTO = (SELECT ROLEID AS roleId FROM ROLEMASTER WHERE ROLENAME = #roleName# AND GROUPID = #groupId#)
                    AND ROLEID NOT IN (SELECT ROLEID As roleIdArr FROM ROLEMASTER WHERE ROLENAME = #roleName# AND GROUPID = #groupId#)
      </update>

Is there is any approach to use select query as inner query in update query?

Thanks & Regards
Ganga Krishna.


**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
for the use of the addressee(s). If you are not the intended recipient, please 
notify the sender by e-mail and delete the original message. Further, you are not 
to copy, disclose, or distribute this e-mail or its contents to any other person and 
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken 
every reasonable precaution to minimize this risk, but is not liable for any damage 
you may sustain as a result of any virus in this e-mail. You should carry out your 
own virus checks before opening the e-mail or attachment. Infosys reserves the 
right to monitor and review the content of all messages sent to or from this e-mail 
address. Messages sent to or from this e-mail address may be stored on the 
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***