How to add a column that contains the id value of the first distinct record for a set of duplicate records

Question

I have the following table named emp

id	fname	lname
1	Mihir	Sheth
2	Mit	Dasondi
3	Narra	Harsha
4	Mihir	Sheth
5	Shrey	Malvi
6	Mit	Dasondi
7	Ujas	Patel

I want to alter the table emp and create a new column NewId, such that for duplicate records it will have the value of the id of the first occurence of distinct record.

For distinct records the value will be null in the NewId column. By the "first occurence of the distinct record" I mean the record that occurs first when sorted on basis of id column.

For a better understanding, please see the following.

id	NewId	fname	lname
1	null	Mihir	Sheth
2	null	Mit	Dasondi
3	null	Narra	Harsha
4	1	Mihir	Sheth
5	null	Shrey	Malvi
6	2	Mit	Dasondi
7	null	Ujas	Patel

Please advise me on how to proceed and also keep in mind that I want the new column to be in the actual table, not in cte or other temporary table.

Answer 1

You need first to add a column to the emp schema:

ALTER TABLE emp ADD NewId INT;

Then you can use a selection of the smallest ids for each employee and update the emp table where the id does not correspond to the minimum id:

UPDATE     emp 
INNER JOIN (SELECT fname, lname, MIN(id) AS id
            FROM emp
            GROUP BY fname, lname) min_emps
        ON emp.fname = min_emps.fname
       AND emp.lname = min_emps.lname
       AND emp.id   <> min_emps.id
SET emp.NewId = min_emps.id;

Here's a MySQL demo, though this may work in sybase too.

---

Edit: Given Sybase documentation on update statements, the corresponding UPDATE statement for your problem may be the following:

UPDATE emp 
SET emp.NewId = min_emps.id;
FROM emp 
JOIN (SELECT fname, lname, MIN(id) AS id  
      FROM emp
      GROUP BY fname, lname             ) min_emps
  ON emp.fname = min_emps.fname
 AND emp.lname = min_emps.lname
 AND emp.id   <> min_emps.id