Inserting increment for each unique value in sql.

Question

Hello, I've been tasked with a SQL problem that is outside of the limited scope of sql knowledge that I have. I have the following problem.

I have a table that currently looks like this:

 widgets
---------
    a
    a
    a
    b
    b
    c
    d
    d
    d

I would like to have another table that has each unique value incrementally numbered... Like so:

widgets  | widget_id
--------- ----------
    a    |    1
    a    |    1
    a    |    1
    b    |    2
    b    |    2
    c    |    3
    d    |    4
    d    |    4
    d    |    4

I'm not sure how this would be done with an insert statement?

Answer 1

Create a table with an IDENTITY column:

something like this:

CREATE TABLE widget_ids (
  widget_id INT AUTO_INCREMENT PRIMARY KEY,
  widget varchar(5)
)

AND run this command:

INSERT INTO
  widget_ids (widget)
SELECT DISTINCT
  widget
FROM
  widgets

---

Now, this is all just an example. If you wanted a long-term solution, you would want to index your 'widget' field in the widget_ids table, and you would want to make sure that a value was entered for each new widget.

---

EDIT: Oh, also, to get the exact table you have listed second, you would left join the widgets table to the widget_ids table:

SELECT
    w.widget,
    wi.widget_id
FROM
    widgets w
LEFT JOIN
    widget_ids wi
  ON
    w.widget = wi.widget

And here, of course, indexing is your friend.

Answer 2

I think that server-side cursors are probably your best option for this. I believe that the following will work:

DECLARE @widgets varchar(20)
DECLARE @widget_id int
DECLARE @widget_count int
DECLARE @index int

DECLARE widgets_cursor CURSOR FOR
SELECT [widgets], COUNT(*)
FROM [original_widgets_table]
GROUP BY [widgets]

SET @widget_id = 0

OPEN widgets_cursor

FETCH NEXT FROM widgets_cursor INTO @widget, @widget_count
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @widget_id = @widget_id + 1
  SET @index = 0

  WHILE @index < @widget_count
  BEGIN
    INSERT [new_widgets_table]
    (
    widget
    , widget_id
    )
    SELECT
    @widget
    , @widget_id

    SET @index = @index + 1
  END

  FETCH NEXT FROM widgets_cursor INTO @widget, @widget_count
END

CLOSE widgets_cursor

DEALLOCATE widgets_cursor

Answer 3

Well, it wouldn't be done with a insert statement.

create a temp table with an auto_increment id and the distinct widget.

Then join the widget table to the temp table, and update the widget with the temp table's id.

What you're doing here, is normalizing your data. Once you have a table of (distinct widget, id), you'd then have any other table that needed to refer to a widget instead refer to the widget id this generates.

Answer 4

My MySQL syntax is not very good, but assuming you've created your new table above, wouldn't something like this work?

set @i = 0; 

create temporary table t_widget_id
as 
    select distinct widgets, @i:=@i+1 as widget_id from widgets

insert into widget_new
    select widgets.widgets,
      t_widget_id.widget_id
     from
      widgets inner join
       t_widget_id 
        on widgets.widgets = t_widget_id.widgets

drop table t_widget_id