1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

update t_row t- what is t?

Discussion in 'SQL PL/SQL' started by monkey, Jun 8, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I think I did not explain what is bothering me:
    what is t in expression update t-row t, and what construction is that?

    http://psoug.org/definition/ROWID.htm

    What did they do in that example?
    Let's say t is alias for t_row, it means we can use alias also in update.
    Why are values of id and rid interchanged?
    Many thanks!!!
     
  2. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Because their example is broken on the web page. Probably a Cut&Paste error. I just re-ran the example and got the expected results.

    Code (Text):
    CREATE TABLE T_ROW (
     ID   NUMBER,
     RID  ROWID);
    table T_ROW created.

    INSERT INTO T_ROW(ID) VALUES(1)
    1 rows inserted.

    UPDATE T_ROW T
    SET RID = T.ROWID;
    1 rows updated.

    SELECT * FROM T_ROW;
    ID  RID
    --- ------------------
      1 AAAK6lAAEAAABKUAAA
     
     
    monkey likes this.
  4. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Many thanks!
    But, what is that statement doinmg at all?
    Would it not rum without this?

    Code (SQL):
    UPDATE T_ROW T
    SET RID = T.ROWID;
    1 ROWS updated.
    Seems like thew renamed the t_row table to t, then set line is just playing with name and making no difference with the final result or?

    Many thanks!!!
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    Here T is used as alias for T_ROW Table and it will take rowid of the each row on that table (T_ROW) and will update the table column RID with ROWID of that each row in that table. This is the reason we aliased the table.
     
    monkey likes this.
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Good point ! I admit this is useless in practice: Oracle has already an built-in pseudocolumn ROWID and you don't need to define an extra column in a table to keep this. I suppose this is just an example ...

    I suppose you mean if we can access the table by ROWID or get the ROWID without doing this update - Yes of course if we use:

    Code (SQL):
    SELECT t.id, t.rowid FROM t_row;
     
    It's not renaming the table t_row to t, but it's an alias, just like we can alias a table in a SELECT statement, we can also alias it in UPDATE or DELETE statements.
     
    monkey likes this.
  7. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    That was right what I wanted to know!
    Please, if we wrote ;

    Code (SQL):

    SELECT id, rowid FROM t_row;
    would we get the same result?

    many thanks!!!
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The point of the example was that in addition to the ROWID column that all Oracle tables have, it is possible to create additional columns of data type ROWID. The example populated the column with the rowid of the one row simply because it was easy to do so. It could almost have easily populated it with the value from a different row in the table.

    Code (Text):
    INSERT INTO T_ROW(ID) VALUES(1);
    INSERT INTO T_ROW(ID) VALUES(2);
    1 rows inserted.
    1 rows inserted.
     
    UPDATE T_ROW T
    SET    RID = (SELECT ROWID FROM t_row WHERE id=2)
    WHERE  id = 1;
    1 rows updated.

    UPDATE T_ROW T
    SET    RID = (SELECT ROWID FROM t_row WHERE id=1)
    WHERE  id = 2;
    1 rows updated.

    SELECT id, rid, rowid
    FROM   t_row;

     ID  RID                ROWID
    ---  ------------------ ------------------
      1  AAAK6rAAEAAABKcAAB AAAK6rAAEAAABKcAAA
      2  AAAK6rAAEAAABKcAAA AAAK6rAAEAAABKcAAB
     
    monkey likes this.
  9. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    !Hello!
    That is the answer to my 2 qestion in one!!!
    MANY THANKS!!!