Archives of the TeradataForum

Message Posted: Wed, 15 Dec 1999 @ 15:48:50 GMT

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

Recently, I saw an example of an SQL construct called Rowid, used within an SQL statement. The example was an attempt to provide an alternate method of doing large amounts of updates to a table, by using an insert/select process instead of an update process. The query is as follows:

     INSERT INTO new_table /* must be new and empty */
      SEL
          CASE WHEN u.rowid NOT NULL THEN u.PK ELSE o.PK END
                       /* may appear multiple times if PK consists of many columns */

          ,CASE WHEN u.rowid NOT NULL THEN u.col1 ELSE o.col1 END col1
                       /* be careful! Do not replace with COALESCE since certain */

          ,CASE WHEN u.rowid NOT NULL THEN u.col2 ELSE o.col2 END col2
                       /* columns might be nullable. E.g. if the update is NULL and */

          ,CASE WHEN u.rowid NOT NULL THEN u.col3 ELSE o.col3 END col3
                       /* the previous value was NOT NULL you would loose the update! */

          ...

          ,o.colx /* in case you don't want to update all columns just get rid of the CASE */

          ...

          ,CASE WHEN u.rowid NOT NULL THEN u.coln ELSE o.coln END coln

      FROM old_table o FULL OUTER JOIN upsert_data_table u
              ON o.PK = u.PK  /* or whatever join criteria you need */

I do understand the concept quite well, however I could not find any information in the SQL manauals about Rowid. Does anyone know where one can procure information about the Rowid construct, its exact meaning and rules for use?

Thanks in advance,

Rob

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