-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|