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***
