CODEWITHSUNDEEP
mysqlsql
Addev
Addev

Reputation: 32233

Autoincrement manually a column MySQL

I have a table with columns:

[id,name,public_id]

actually the table contains

[1,john,0]
[2,sara,0]
[3,jack,0]
.....

I want to change the third column to 1050,1051,1052....

[1,john,1050]
[2,sara,1051]
[3,jack,1052]
.....

How can I make that update?

Some considerations: The public id must be over 1049 and must be consecutive. For example for 100 rows the public_id must be [1050....1149]

Thanks in advance

Upvotes: 0

Views: 2074

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Assuming your table's name is TEST_TABLE, this MySQL syntax will update PUBLIC_ID with consecutive values starting from 1500 (and in order of ID):

REPLACE INTO TEST_TABLE
SELECT TEST_TABLE.ID, TEST_TABLE.NAME, @ROWNUM := @ROWNUM + 1
FROM
    TEST_TABLE,
    (SELECT @rownum := 1499) R
ORDER BY ID

In plain English:

  • For each row, figure out its order when data is sorted by ID. Call this order "ROWNUM".
  • Put ROWNUM (+ starting offset) back to the table instead of the original PUBLIC_ID.

WARNING: According to MySQL documentation, REPLACE will actually delete a duplicated row before inserting it again (instead of just updating modified fields), which my be an issue in the presence of foreign keys.

(Not sure about SQLite, but I'm guessing you could employ a similar general idea.)

Upvotes: 1

Rene Pot
Rene Pot

Reputation: 24815

You can do an update like this:

UPDATE table SET public_id = id + 1049

With this, you are using the ID, and adding 1049 to it to get the result you need

Upvotes: 5

Related Questions