How to populate new column starting from 400000 in MySQL

user_14693453 0
Viewed 34

I'm working on a homework for college, i need to set values to a new created column but it must start from a specific number, in this case 40000000, so it will be something like this:

ID ║ Column to populate ║ FirstName ║ LastName ║
1     40000000             John        Smith
2     40000001             John        Walker
3     40000002             John        Locke
and etc...

I guess it's something related to the following query:

UPDATE [table_name] SET [column_name] = [column_name] + 1;

But this clearly won't start from the number i want.

Maybe it's a dumb question, but i googled it and i couldn't find anything related to what i need, so any help is really appreciated. Thanks!!!

mariadb sql-update mysql sql
2 Answers

Here is one option using window functions:

update mytable t
inner join (
    select id, row_number() over(order by id) rn
    from mytable t1
) t1 on t1.id = t.id
set t.new_col = 3999 + t1.rn

Just in case: if id starts at 1 and increments without gaps, then it is even simpler:

update mytable set new_col = 3999 + id

I don't think this is safe, though: id looks like an auto_increment column, and such column does not guarantee no gap.

user_106767160

And you want there to be no gaps in the numbers? It will be folly to use AUTO_INCREMENT.

Virtually any INSERT that has any kind of hiccup will "burn" a number, thereby leaving a gap.

Hence, you cannot assume AUTO_INCREMENT will provide any kind of clean sequence of numbers.

If you don't care about gaps, then ALTER TABLE t AUTO_INCREMENT=40000000 is a simple way for future inserts to be at least that big. But there could be gaps.

Perhaps less than one table in 10,000 plays that game. So, I respectfully suggest, that it is a useless exercise for someone learning MySQL/MariaDB.

user_17668310
Related