locked
Update Query - Table X is ambiguous RRS feed

  • Question

  • Hi 

    I am trying to migrate the following Oracle query to SQL SERVER. But getting following error


    The table 'S_IMP_FILES' is ambiguous.

    ORACLE Query:

         

    UPDATE S_IMP_FILES A
      SET A.PARENT_ID = (SELECT B.FILE_ID
    FROM S_IMP_FILES B
                          WHERE B.NAMESPACE = A.NAMESPACE
                          AND B.LOOKUP_KEY = A.PARENT_LOOKUP_KEY
                           AND B.PATH_DEPTH = (A.PATH_DEPTH - 1))
    WHERE A.PARENT_ID IS NULL
       AND A.PATH_DEPTH > 0

    SQL SERVER Query:

       UPDATE  S_IMP_FILES
       SET S_IMP_FILES.PARENT_ID = A.FILE_ID
       FROM S_IMP_FILES B 
                INNER JOIN S_IMP_FILES A
                                ON B.NAMESPACE = A.NAMESPACE
                              AND B.LOOKUP_KEY = A.PARENT_LOOKUP_KEY
                              AND B.PATH_DEPTH = (A.PATH_DEPTH - 1)
       WHERE A.PARENT_ID IS NULL
       AND A.PATH_DEPTH > 0

    how to solve this error?

    Thanks



    Tuesday, May 19, 2020 10:04 PM

Answers

  •    FROM S_IMP_FILES

               INNER JOIN S_IMP_FILES A

    When you define an alias then you must use the alias everywhere, also in the UPDATE clause. Which one do want do update, A or B?

    If it's B =>

       UPDATE B
       SET S_IMP_FILES.PARENT_ID = A.FILE_ID
       FROM S_IMP_FILES B 
                INNER JOIN S_IMP_FILES A
                                ON B.NAMESPACE = A.NAMESPACE
                              AND B.LOOKUP_KEY = A.PARENT_LOOKUP_KEY
                              AND B.PATH_DEPTH = (A.PATH_DEPTH - 1)
       WHERE A.PARENT_ID IS NULL
       AND A.PATH_DEPTH > 0 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, May 20, 2020 6:24 AM
  • Hi ,

    We cannot use table which is in update clause more than one times in from clause. Try:

    UPDATE B
       SET PARENT_ID = A.FILE_ID
       FROM S_IMP_FILES B 
                INNER JOIN S_IMP_FILES A
                                ON B.NAMESPACE = A.NAMESPACE
                              AND B.LOOKUP_KEY = A.PARENT_LOOKUP_KEY
                              AND B.PATH_DEPTH = (A.PATH_DEPTH - 1)
       WHERE A.PARENT_ID IS NULL
       AND A.PATH_DEPTH > 0

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by KrishnaPartha Wednesday, May 20, 2020 4:14 PM
    Wednesday, May 20, 2020 8:47 AM

All replies

  • Hi KrishnaPartha,

    It would be great if you could provide a minimal reproducible example:
    (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
    (2) What you need to do, i.e. logic.
    (3) Desired output based on the sample data from #1 above.
    (4) Your SQL Server version (SELECT @@version;)

    Tuesday, May 19, 2020 10:48 PM
  •    FROM S_IMP_FILES

               INNER JOIN S_IMP_FILES A

    When you define an alias then you must use the alias everywhere, also in the UPDATE clause. Which one do want do update, A or B?

    If it's B =>

       UPDATE B
       SET S_IMP_FILES.PARENT_ID = A.FILE_ID
       FROM S_IMP_FILES B 
                INNER JOIN S_IMP_FILES A
                                ON B.NAMESPACE = A.NAMESPACE
                              AND B.LOOKUP_KEY = A.PARENT_LOOKUP_KEY
                              AND B.PATH_DEPTH = (A.PATH_DEPTH - 1)
       WHERE A.PARENT_ID IS NULL
       AND A.PATH_DEPTH > 0 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, May 20, 2020 6:24 AM
  • Hi ,

    We cannot use table which is in update clause more than one times in from clause. Try:

    UPDATE B
       SET PARENT_ID = A.FILE_ID
       FROM S_IMP_FILES B 
                INNER JOIN S_IMP_FILES A
                                ON B.NAMESPACE = A.NAMESPACE
                              AND B.LOOKUP_KEY = A.PARENT_LOOKUP_KEY
                              AND B.PATH_DEPTH = (A.PATH_DEPTH - 1)
       WHERE A.PARENT_ID IS NULL
       AND A.PATH_DEPTH > 0

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by KrishnaPartha Wednesday, May 20, 2020 4:14 PM
    Wednesday, May 20, 2020 8:47 AM
  • The various proprietary "UPDATE .. FROM.." syntaxes have different semantics. Either stick to the standard ANSI/ISO update syntax or move over to the standard ANSI ISO merge statement.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, May 20, 2020 5:43 PM