Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>  

Subj:   Rowid SQL Statement
From:   Rob Grothaus

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 */
          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,


  <Prev Next>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020