Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    5

    Unanswered: Oracle equivalent SQL required (URGENT)

    Can you help me to convert the below MS SQL to Oracle equivalent?

    UPDATE tblA A INNER JOIN tblB B ON (A.TRAN_REF_NO = B.TRAN_REF_NO) AND (A.LINE_TYPE = B.LINE_TYPE) SET A.FLAG = 'U'
    WHERE (((B.UPDT_ACT_CD)='I'));


    I tried the below code but I'm getting error.

    UPDATE tblA A SET FLAG = 'U'
    WHERE exists
    (select * from tblBB
    WHERE
    (A.TRAN_REF_NO = B.TRAN_REF_NO) AND
    (A.LINE_TYPE = B.LINE_TYPE) AND ((B.UPDT_ACT_CD)='I'))

    Please provide me the correct SQL which could update based on the inner join condition. It's urrgent and any immediate hellp would be great.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I can't spot anything obvious. What is the error you are getting (it general helps to tell us that right away!)

    BTW, what's with all the parentheses here?
    Code:
    WHERE (((B.UPDT_ACT_CD)='I'))
    What's wrong with:
    Code:
    WHERE B.UPDT_ACT_CD='I'
    ?!

  3. #3
    Join Date
    Mar 2005
    Posts
    5

    Oracle equivalent of SQL............

    UPDATE tblA A INNER JOIN tblB B ON (A.TRAN_REF_NO = B.TRAN_REF_NO) AND (A.LINE_TYPE = B.LINE_TYPE) SET A.FLAG = 'U'
    WHERE (((B.UPDT_ACT_CD)='I')); is the Access query that will work with SQL Server.
    The paranthesis can be removed in WHERE (((B.UPDT_ACT_CD)='I')). I'm not confident with the oracle version of the code that was hit with error. I need a Oracle equivalent of the above SQL.

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    try this:
    Code:
    UPDATE tblA A 
    SET FLAG = 'U'
    WHERE exists (select * 
                        from tblB B
                        WHERE A.TRAN_REF_NO = B.TRAN_REF_NO 
                        AND A.LINE_TYPE = B.LINE_TYPE 
                        AND B.UPDT_ACT_CD='I')
    the only difference is you missed space between table name and its alias

Posting Permissions

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