Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: Oracle query required

    Hi,
    Please help me ingiving sql server query into oracle.
    query is like this,


    UPDATE Transactions
    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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?
    Dave

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    It is almost always easier to use the merge statement for this type of thing in Oracle.

    Code:
    MERGE INTO transactions t
    USING (
      SELECT blah
      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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •