-1
 ALTER TABLE advance_d ADD testcolumn INTEGER NOT NULL AUTO_INCREMENT;

This is the sql code i typed. i have a table called advace_d. It has a primary key column called "ad_id" . when i wrote this code ALTER TABLE advance_d ADD testcolumn INTEGER; a new column was created with null values. I want to know how to create the column with values starting from a given value auto incremented by 1.

so my table would have 1 primary key column for ID and another column with values starting from 50 incremented by 1.

Stewie
  • 27
  • 8
  • With regard to _which_ column do you want this new sequence to begin? I think we need to know this to give any kind of meaningful answer. – Tim Biegeleisen Aug 27 '18 at 05:43
  • 1
    Possible duplicate of [Change auto increment starting number?](https://stackoverflow.com/questions/970597/change-auto-increment-starting-number) – Matt Clark Aug 27 '18 at 05:44
  • Adding row numbers... This sounds like a task you should do in a query or at the application level instead of in the data itself. – We Are All Monica Aug 27 '18 at 05:45
  • Looking to add a new column to a table with an existing primary key column. So when i try to add a column with auto increment it says Incorrect table definition; there can be only one auto column and it must be defined as a key – Stewie Aug 27 '18 at 05:46
  • @MattClark not looking to change the auto increment value of a column. its more like i wanted to create a new column with a customized auto increment value rather than changing something existing – Stewie Aug 27 '18 at 05:48
  • @TimBiegeleisen the table contains 1 column called "ad_id" which is set as primary key with auto increment. I want to add a new column to this table where i auto increment values starting from row1 incremented by 1 for each row. is it possible? – Stewie Aug 27 '18 at 05:53
  • There's just no good reason why you'd ever want or need to do this – Strawberry Aug 27 '18 at 06:18

1 Answers1

2

The only way I know how to do this is via insertion into a table with an auto increment column. I don't know how to do this with your existing table, but you can create a new one, and then copy the previous table over to it, populating the auto increment column in the process:

CREATE TABLE newTable (col1, col2, ..., colAuto INT NOT NULL AUTO_INCREMENT);
INSERT INTO newTable (col1, col2, ..., colAuto)
SELECT col1, col2, ..., NULL   -- NULL for colAuto
FROM yourTable
ORDER BY ad_id;

This should result in a new table with the same data, and a column colAuto which starts as 1, as ordered by the ad_id column in your original table. After this, you may alter colAuto if you don't want it to be auto increment anymore. You may also delete the original table if it no longer serves any purpose.

Edit:

We might also be able to do this using a row number session variable:

SET @rn := 0;
UPDATE yourTable
SET colAuto = (SELECT @rn := @rn + 1 )
ORDER BY ad_id;

But in practice, doing such an update into a non auto increment column may not make much business sense. This is because as soon as you add more data to your table, you would have to manually update again, but this time the sequence would be starting from some number other than zero. Really, an auto increment column is the way to go for easily maintaining a sequence in SQL.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hi Tim. what i am trying to do is, add a new column to an existing table and make the values in the column start from a given value say 50? then increment 50 by 1 and so on for successive rows. So my column would have 50, 51, 52... and so on – Stewie Aug 27 '18 at 06:00
  • ...and, why can't my solution do that? Just do another update after running my answer which adds 49 to everything in `colAuto`. – Tim Biegeleisen Aug 27 '18 at 06:01
  • could i not do this without creating a table? Using select/alter statements – Stewie Aug 27 '18 at 06:02
  • Will try it out and get back – Stewie Aug 27 '18 at 06:02
  • Let me get this right, we cannot alter existing data in a table with a new auto increment value but assigning new auto increment value works on new data that you add to a table. Auto increment works when creating a column. I tried this out and it works. – Stewie Aug 27 '18 at 06:09
  • 1
    Sure you can do it in your existing table, but that would mean using session variables (dynamic SQL). I doubt that would sit well with you, so I suggested this instead. – Tim Biegeleisen Aug 27 '18 at 06:10
  • Well, my problem is solved nonetheless. I am okay with this. New to SQL, so do not want to get myself in the middle of the sea. Thanks Tim for your answer. – Stewie Aug 27 '18 at 06:11
  • 1
    I guess this could be done by adding the column (without any constraint), updating each row using an incremental value (using a cursor probably) then adding the constraint. This is a bit more work but doesn't required to copy the full table. – AxelH Aug 27 '18 at 06:11
  • @AxelH Yup, you could do that. – Tim Biegeleisen Aug 27 '18 at 06:12
  • @AxelH yes that works. I tried that out also but as you said it takes time. And wanted to know if it was possible to add incremental values. My column values aren't incremented by different values, just by 1 per row. – Stewie Aug 27 '18 at 06:13
  • 1
    @Stewie Have given you an option for using session variables to do the update. Whether you prefer it to the earlier is your preference. – Tim Biegeleisen Aug 27 '18 at 06:24