how to add a column that contains the id value of the first distinct record for a set of duplicate r-CodePudding

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

Now i want to alter the table emp and create a new column NewId such that for duplicate records it will have value of the id of the first occurence of distinct record. And for distinct records the value will be null in NewId column. By the first occurence of the distinct record I mean the record that occurs first when sorted on basis of id column. Refer following for better understanding.

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 suggest me some way to do this. And I want the new column in the actual table itself and not in cte or some temporary table. So please consider that as well.

CodePudding user response：

We can try using MIN as an analytic function here:

SELECT id, CASE WHEN id <> MIN(id) OVER (PARTITION BY fname, lname)
                THEN MIN(id) OVER (PARTITION BY fname, lname) END AS NewId,
       fname, lname
FROM emp
ORDER BY id;

CodePudding user response：

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