how to update a column for all rows in a table with different values for each row in a table of 100 records

Question

my table contains 100 records and i need to update a column with new values(diff value for each record) for each of the 100 records .How can i do this. the column to update the values is not primary key.

Answer 1

UPDATE tablename
   SET columnname = CASE id
                        WHEN 1 THEN 42
                        WHEN 2 THEN 666
                    END

With this query columnname will be updated to 42 for the row with id = 1, and 666 for id = 2

Answer 2

Create a table with an autoicrement id and the columns of the original table.

Then

INSERT INTO new_table (column1, column2,.....) -- refer all columns except autoincrement id
SELECT * FROM old_table

Update the old table by joining with the new, assuming the is a key composite or not that distincts each row