Need a simple SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Need a simple SQL

  1. #1
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi Gurus,

    I have a table with data like

    SQL> select * from emp_name;

    EMP_ID NAME
    --------- --------------------
    100 Johnny
    100 Johnny
    100 Johnny

    I need to set the NAME (Johhny here) to null for all occurrences of emp_id except the first.

    That is, I need

    SQL> select * from emp_name;

    EMP_ID NAME
    --------- --------------------
    100 Johnny
    100
    100



    EMP_ID can occur multiple times in the table and each one would store the same name. I need to retain the emp_id because it maps to other fields also but need to eliminate the NAME in all except the first occurrence.


    Thanks!
    Hemant

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    No need for nulls - you can break on the name.

    SQL> break on ename
    SQL> select * from emp3;

    EMP_NO ENAME
    ---------- ----------------
    100 Johnny
    100
    100

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by hemanr
    I need to retain the emp_id because it maps to other fields also but need to eliminate the NAME in all except the first occurrence.
    How do you define the first occurance? Do you have any timestamp column in the same table? The rows are not stored in any order in Oracle tables.

    Sanjay

  4. #4
    Join Date
    May 2001
    Location
    Atlanta US
    Posts
    262
    Hi,

    Thanks,

    I used rowid to do the update.

    update tableA a set name = 'Johnny' where a.rowid > (select min(b.rowid) from tableA b where a.emp_id = b.emp_id and a.emp_name = b.emp_name);
    Hemant

Posting Permissions

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



Click Here to Expand Forum to Full Width