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
解决方案
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.)