Need a simple SQL

[hemanr]

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!

[stecal]

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

[SANJAY_G]

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

[hemanr]

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);