I have gotten data from an external source that I need to use for an application. However this data isn't very clean. Namely, I have a table T, where T.id is not unique throughout the table. However, given 2 lines with the same id, they DO refer to different things. Hence, as I do not wish to lose data, I am trying to give different id values to those rows.
For example, say I start with the following records in my table (x1 : id, x2 : value) :
(1,3) ; (2,5) ; (3,1) ; (1,2)
I would like to get the following
(1,3) ; (2,5) ; (3,1) ; (4,2) where 4 = max(id) + 1
I don't know how to do such kind of programming on a SQL table. Can anybody help ?
Best :)
You could create a different table with an auto_increment column for the ID, and INSERT to that table the values of "value". This would give them unique id's. (I'm generalizing since i don't know which flavour of sql you are using).
CREATE table NEWTABLE (id <auto_incremented_column>, value <type>);
INSERT INTO newTable(value)
SELECT value FROM OLD_TABLE;
For example in mysql would be something like this:
CREATE TABLE table2 (
id INT auto_increment NOT NULL,
value INT,
PRIMARY KEY (id)
);
INSERT INTO table2 (value)
SELECT value
FROM table1;