Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    5

    Unanswered: How to update using table in 2nd level temporary table?

    When I try to update T1 with the value comparing with T2, I got the error

    SQL0204N "T1.Col5" is an undefined name. SQLSTATE=42704

    I found it should caused by referring T1 in 2nd level temporary table, my question is how can I refer T1 in there?

    Code:
    UPDATE
    	Table1 T1
    SET
    	Col1 = 
    		(
    			SELECT
    				Col2
    			FROM
    				(
    					SELECT
    							Col2
    					FROM
    							T2
    					WHERE
    						T1.Col5 = T2.Col5
    				)
    				T (Col2)
    		)
    Thank you!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    update t1 as t1 set (col1) = (select t2.col2 from t2 as t2 where t1.col5 = t2.col5)
    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with Andy.

    It is not neccesary to use the nested subquery in your query.

    If your query was more complex and it might be neccesary to use nested subqueries,
    consider to use LATERAL keyword, like...
    Code:
    UPDATE Table1 T1
     SET   Col1
           = (SELECT Col2
               FROM  LATERAL
                     (SELECT Col2
                       FROM  T2
                       WHERE T1.Col5 = T2.Col5
                     ) T (Col2)
             )
    ;

    Please see the followings for the details.

    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows
    ...
    ...

    table-reference
    Code:
    ...
    ...
    
    nested-table-expression
    
    |--+-----------------------------------------------+--(fullselect)--|
       |         (3)                                   |                 
       '-LATERAL------+------------------------------+-'                 
                      '-| continue-handler |--WITHIN-'
    ...

    Notes:

    3. TABLE can be specified in place of LATERAL.

    ...
    ...

    Correlated references in table-references

    Correlated references can be used in nested table expressions or as arguments to table functions.
    The basic rule that applies for both these cases is that the correlated reference must be from a table-reference at a higher level in the hierarchy of subqueries.
    This hierarchy includes the table-references that have already been resolved in the left-to-right processing of the FROM clause.
    For nested table expressions, the LATERAL keyword must appear before the fullselect.
    So the following examples are valid syntax:

    ...

Posting Permissions

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