I spent all day creating then manually inserting into some tables I created today that look like this:
ID - Int (Primary Key set to Auto Increment)
Value - Varchar
But then I realized I had forgotten to insert a value of "--" into the first row of each table.
Is it possible to maybe add 1 to the ID no for each of the values currently in the table then insert the "--" value into the first row?
One of the ways to fix it is to update the record with the ID=1 to '--':
update yourTable set Value = '--' where id = 1
Then you will be required to re-insert the first record into the table:
INSERT INTO yourTable (Value)
VALUES('the value that was originally inserted as 1')
However, if the order of the already inserted records is important then you can insert the '--' value as the ID = 0. In this case you need to disable the IDENTITY column using the SET IDENTITY_INSERT:
SET IDENTITY_INSERT yourTable ON
INSERT INTO yourTable (ID, Value)
VALUES(0, '--')
SET IDENTITY_INSERT yourTable OFF
This way the order of inserted records will be preserved and the '--' will be inserted with the ID of 0
BTW, for mySQL you can insert into the IDENTITY column by default
Because Your ID column auto increment (IDENTITY), when you insert, you mustn't insert ID column. To insert your table, you just insert other columns.
Code insert like this:
INSERT INTO Your_Table (ColA, ColB, ...) -- `except identity colums`
VALUES (A, B, ...)
INSERT INTO Your_Table
VALUES (A, B, ...) -- except identity colums
INSERT INTO Your_Table
SELECT ColA, ColB, ... FROM Other_Table -- except identity colums
If Your_Table empty:
INSERT INTO Your_Table (Value)
VALUES ('--')
INSERT INTO Your_Table
SELECT '--'
If not:
UPDATE Your_Table
SET Value = '--'
WHERE ID = 1
------------------------ More Infor -------------------------------------------
If you want to first row have ID = 1. You can set ID column have IDENTITY(1,1). Like this:
CREATE TABLE Your_Table
(
ID INT IDENTITY(1, 1) PRIMARY KEY,
Value VARCHAR(50) NULL
)
Or, After you create Your_Table, you can set right-click Your_Table, select Design, select ID column. Look Column Properties below, expand Identity Specification, Double click (Is identity), then set value Identity Increment and Identity Seed
--into a numeric column. Also, what database are you using SQL Server or MySQL? Please tag appropriately.mysqltag due to tsql. Tweak accordingly.Update yourTable set Value = '--' where id = 1You may need to re-insert the value that was with ID of 1