CODEWITHSUNDEEP
mysqlsql-update
user2454281
user2454281

Reputation: 73

how to update mysql table, i want to regenerate serial no for existing table

I have mysql table of approx 500 records, and some how i have modified my table as per my requiremens. However I have one table with fields like (sale_id,cust_id,Sales_desc bla bla)

I have made sale_id row with one value('1') for all 500rows,,,,,,but what i want to know from you guys, can any one tell me how update sale_id with 1 to 500,,,,,i mean to say i want this row should start from 1 to N,,,,,but there is already some data in this table,,,,,i just wanted to replace existing sale_id value with serial no(1,2,3,4,5....n) plz help me with ths question...i am waiting....

shahid emsoftware.in

Upvotes: 0

Views: 5357

Answers (2)

The Scrum Meister
The Scrum Meister

Reputation: 30121

This may do the trick:

UPDATE tableName, (SELECT @id := 0) dm
SET sale_id = (@id := @id + 1)

Upvotes: 7

Manzabar
Manzabar

Reputation: 676

It sounds like sale_id in your table should be the primary key on the table and be set to auto-increment. Since it also sounds like there's not a lot of data already in the table, why not recreate it with the sale_id setup to handle this for you, insert the data into the new table and then rename the tables to swamp them?

CREATE TABLE `TableNew` LIKE `TableOld`;
ALTER TABLE `TableNew` MODIFY COLUMN `sale_id` INTEGER  NOT NULL DEFAULT 0 AUTO_INCREMENT;
INSERT IGNORE INTO `TableNew` (cust_id, Sales_desc, bla, bla) SELECT cust_id, Sales_desc, bla, bla FROM `TableOld`;
RENAME TABLE `TableOld` to `TableOldBackup`;
RENAME TABLE `TableNew` to `TableOld`;

Note: In the insert statement given in this answer, you must specify all the fields to be copied over and exclude sale_id from the insert. That way mysql will populate sale_id with the auto-incremented values.

Upvotes: 1

Related Questions