Please help me ingiving sql server query into oracle.
query is like this,
SET ROLE_NAME=C.ROLE_NAME FROM Transactions ET,
(SELECT EMR_TRANSACTION_ID,EUR.ROLE_NAME,ET.USER_LOGIN,PAT IENT_ID,
TASK_ID,ID_VALUE,FLOW_DETAILS_ID,IS_EXPRESS FROM Transactions ET INNER JOIN UserRoleLocation EUR ON ET.USER_LOGIN=EUR.USER_LOGIN WHERE
TASK_ID=34 ) C
WHERE C.EMR_TRANSACTION_ID=ET.EMR_TRANSACTION_ID AND ET.TASK_ID=34
This sql loos like a huge mess. For one you are selecting c.column from a table that you have aliased as ET, you are giving the same table name the same alias in multiple parts of the query, using a comma in the set clause, but having a nested query instead of the column you wish to set. How about you give a written description of what you really want to happen via this update statement?
It is almost always easier to use the merge statement for this type of thing in Oracle.
MERGE INTO transactions t
FROM transactions et
JOIN UserRoleLocation loc ON et.user_login = loc.user_login
) sub ON (t.emr_transaction_id = sub.emr_transaction_id)
WHEN MATCHED THEN UPDATE
SET role_name = sub.role_name