Archives of the TeradataForum

Message Posted: Mon, 12 Nov 2007 @ 08:12:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

All,

I'm facing a peculiar phenomenon with Global temp table when I try to update the same.

Global temp tables are created with on commit preserve rows and they are all populated and trying to update in the same session.

(Have checked temp table is populated when updating)

Please don't worry too much about the syntax of update and where condition (these are the ones input from application). All these are done thru stored proc called from JDBC. Even when I test these stand alone in queryman / bteq the result is the same (TD version 6.2 demo).

The below are my scenarios?

     UPDATE GT_TABLE T1
     SET COL1 =  (COALESCE(T1.COL1,'') ||';Default') ,
     COL2  = 'ERROR'
     WHERE T1.COL1 = T1.COL1..

When I run the above, it says 0 rows processed, it is basically not updating anything.

Where as

     UPDATE GT_TABLE
     SET COL1 =  (COALESCE(GT_TABLE.COL1,'') ||';Default') ,
     COL2  = 'ERROR'
     WHERE GT_TABLE.COL1 = GT_TABLE.COL1.

...works fine and columns get updated appropriately

still further to my musings both the statements work fine for a normal permanent table.

Is this the way global temp tables are designed to work or am I missing something?

Is aliasing of global temp table not allowed in update though aliasing work fine for a select?

Thanks in advance for your help / suggestions.

	<Prev	Next>		<<First	<Prev	Next>	Last>>