1

I have a table like this.

ID  Name      Source ID
1   Orange       0
2   Pear         0
3   Apple        0
4   Orange       0
5   Apple        0
6   Banana       0
7   Orange       0

What I want to do is:

  • For the records with FIRST occurrence of "Name", I want to update the "Source Id" with the "Id" value
  • For the records with SECOND and CONSECUTIVE occurrences of "Name", I want to update the "Source Id" with the "Id" value of the FIRST occurrence

So, the table should be updated as follows:

ID  Name      Source ID
1   Orange       1
2   Pear         2
3   Apple        3
4   Orange       1
5   Apple        3
6   Banana       6
7   Orange       1

How can I do it in SQL (Oracle to be in particular, but I'm fine with General SQL as well) ...

Thanks!

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jag
  • 89
  • 3
  • 8

3 Answers3

1
UPDATE MyTable
SET SourceID = Sub.ID
FROM MyTable 
INNER JOIN (SELECT MIN(ID) as ID, Name FROM MyTable GROUP BY Name) Sub
    ON Sub.Name = MyTable.Name

Just use a subquery that lists the min id per name.

JNK
  • 63,321
  • 15
  • 122
  • 138
1

Since ID is growing autoincrement value (right?) FirstID could be calculated as MIN(ID):

UPDATE fruits
  SET SourceID = ag.ID
FROM fruits f 
INNER JOIN
(
  SELECT MIN(ID) as ID, Name FROM @fruits
  GROUP BY Name
) ag 
ON ag.Name = f.Name
sll
  • 61,540
  • 22
  • 104
  • 156
1

Something like this should get you what you want:

update table a
set source_id = (
    select min(id)
    from table b
    where b.name = a.name
);
Craig
  • 5,740
  • 21
  • 30