How to Add Auto Incremental Identity Column to Table with data
How to Add Auto Incremental Identity Column to Table with data. ie. New Added column fill with ids for old data
Old user table with data
Name, email,
abc, abc@gmail.com
abcd, abcd@gmial.com
EXPECTED OUTPUT
id, Name, email,
1, abc, abc@gmail.com
2, abcd, abcd@gmial.com
mysql sql database
edited Jan 1 at 10:00
iminiki
8091020
asked Jan 1 at 8:54
ArtierArtier
1,0711316
add a comment |
How to Add Auto Incremental Identity Column to Table with data. ie. New Added column fill with ids for old data
Old user table with data
Name, email,
abc, abc@gmail.com
abcd, abcd@gmial.com
EXPECTED OUTPUT
id, Name, email,
1, abc, abc@gmail.com
2, abcd, abcd@gmial.com
mysql sql database
edited Jan 1 at 10:00
iminiki
8091020
asked Jan 1 at 8:54
ArtierArtier
1,0711316
1
add a proper data sample and the expected result
– scaisEdge
Jan 1 at 8:58
add a comment |
How to Add Auto Incremental Identity Column to Table with data. ie. New Added column fill with ids for old data
Old user table with data
Name, email,
abc, abc@gmail.com
abcd, abcd@gmial.com
EXPECTED OUTPUT
id, Name, email,
1, abc, abc@gmail.com
2, abcd, abcd@gmial.com
mysql sql database
edited Jan 1 at 10:00
iminiki
8091020
asked Jan 1 at 8:54
ArtierArtier
1,0711316
How to Add Auto Incremental Identity Column to Table with data. ie. New Added column fill with ids for old data
Old user table with data
Name, email,
abc, abc@gmail.com
abcd, abcd@gmial.com
EXPECTED OUTPUT
id, Name, email,
1, abc, abc@gmail.com
2, abcd, abcd@gmial.com
mysql sql database
mysql sql database
edited Jan 1 at 10:00
iminiki
8091020
asked Jan 1 at 8:54
ArtierArtier
1,0711316
edited Jan 1 at 10:00
iminiki
8091020
asked Jan 1 at 8:54
ArtierArtier
1,0711316
edited Jan 1 at 10:00
iminiki
8091020
edited Jan 1 at 10:00
iminiki
8091020
edited Jan 1 at 10:00
iminiki
8091020
8091020
asked Jan 1 at 8:54
ArtierArtier
1,0711316
asked Jan 1 at 8:54
ArtierArtier
1,0711316
asked Jan 1 at 8:54
ArtierArtier
1,0711316
1,0711316
1
add a proper data sample and the expected result
– scaisEdge
Jan 1 at 8:58
add a comment |
1
add a proper data sample and the expected result
– scaisEdge
Jan 1 at 8:58
1
1
add a proper data sample and the expected result
– scaisEdge
Jan 1 at 8:58
add a proper data sample and the expected result
– scaisEdge
Jan 1 at 8:58
add a comment |
2 Answers
2
active
oldest
votes
You can write:
ALTER TABLE MY_TABLE ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
answered Jan 1 at 9:09
iminikiiminiki
8091020
add a comment |
Alter table does no allow you to order the new field so perhaps you need to do it in 2 stages. For example
DROP TABLE IF EXISTS T;
CREATE TABLE T (NAME VARCHAR(10),EMAIL VARCHAR(20));
INSERT INTO T VALUES('ABC','abc@gmail.com'),('ABCD','abcd@gmial.com');
ALTER TABLE T
ADD COLUMN ID INT FIRST;
UPDATE T JOIN(
SELECT NAME, ROW_NUMBER() OVER (ORDER BY NAME DESC) RN FROM T) s
ON S.NAME = T.NAME
SET T.ID = S.RN
;
ALTER TABLE T
MODIFY COLUMN ID INT AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM T;
Where id is added and then updated for existing records using row_number with order by
With this result
+----+------+----------------+
| ID | NAME | EMAIL |
+----+------+----------------+
| 1 | ABCD | abcd@gmial.com |
| 2 | ABC | abc@gmail.com |
+----+------+----------------+
2 rows in set (0.00 sec)
The second Alter statement then modifys id.
show create table t;
CREATE TABLE `t` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(10) DEFAULT NULL,
`EMAIL` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
If you are not on mysql version 8 or above you will need to replace the row_number bit with row number simulation using variables.
Not that it makes any difference anyway since any query would still need to include an order by to get the outcome you want.
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994174%2fhow-to-add-auto-incremental-identity-column-to-table-with-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can write:
ALTER TABLE MY_TABLE ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
answered Jan 1 at 9:09
iminikiiminiki
8091020
add a comment |
You can write:
ALTER TABLE MY_TABLE ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
answered Jan 1 at 9:09
iminikiiminiki
8091020
add a comment |
You can write:
ALTER TABLE MY_TABLE ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
answered Jan 1 at 9:09
iminikiiminiki
8091020
You can write:
ALTER TABLE MY_TABLE ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
answered Jan 1 at 9:09
iminikiiminiki
8091020
answered Jan 1 at 9:09
iminikiiminiki
8091020
answered Jan 1 at 9:09
iminikiiminiki
8091020
answered Jan 1 at 9:09
iminikiiminiki
8091020
8091020
add a comment |
add a comment |
Alter table does no allow you to order the new field so perhaps you need to do it in 2 stages. For example
DROP TABLE IF EXISTS T;
CREATE TABLE T (NAME VARCHAR(10),EMAIL VARCHAR(20));
INSERT INTO T VALUES('ABC','abc@gmail.com'),('ABCD','abcd@gmial.com');
ALTER TABLE T
ADD COLUMN ID INT FIRST;
UPDATE T JOIN(
SELECT NAME, ROW_NUMBER() OVER (ORDER BY NAME DESC) RN FROM T) s
ON S.NAME = T.NAME
SET T.ID = S.RN
;
ALTER TABLE T
MODIFY COLUMN ID INT AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM T;
Where id is added and then updated for existing records using row_number with order by
With this result
+----+------+----------------+
| ID | NAME | EMAIL |
+----+------+----------------+
| 1 | ABCD | abcd@gmial.com |
| 2 | ABC | abc@gmail.com |
+----+------+----------------+
2 rows in set (0.00 sec)
The second Alter statement then modifys id.
show create table t;
CREATE TABLE `t` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(10) DEFAULT NULL,
`EMAIL` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
If you are not on mysql version 8 or above you will need to replace the row_number bit with row number simulation using variables.
Not that it makes any difference anyway since any query would still need to include an order by to get the outcome you want.
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
add a comment |
Alter table does no allow you to order the new field so perhaps you need to do it in 2 stages. For example
DROP TABLE IF EXISTS T;
CREATE TABLE T (NAME VARCHAR(10),EMAIL VARCHAR(20));
INSERT INTO T VALUES('ABC','abc@gmail.com'),('ABCD','abcd@gmial.com');
ALTER TABLE T
ADD COLUMN ID INT FIRST;
UPDATE T JOIN(
SELECT NAME, ROW_NUMBER() OVER (ORDER BY NAME DESC) RN FROM T) s
ON S.NAME = T.NAME
SET T.ID = S.RN
;
ALTER TABLE T
MODIFY COLUMN ID INT AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM T;
Where id is added and then updated for existing records using row_number with order by
With this result
+----+------+----------------+
| ID | NAME | EMAIL |
+----+------+----------------+
| 1 | ABCD | abcd@gmial.com |
| 2 | ABC | abc@gmail.com |
+----+------+----------------+
2 rows in set (0.00 sec)
The second Alter statement then modifys id.
show create table t;
CREATE TABLE `t` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(10) DEFAULT NULL,
`EMAIL` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
If you are not on mysql version 8 or above you will need to replace the row_number bit with row number simulation using variables.
Not that it makes any difference anyway since any query would still need to include an order by to get the outcome you want.
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
add a comment |
Alter table does no allow you to order the new field so perhaps you need to do it in 2 stages. For example
DROP TABLE IF EXISTS T;
CREATE TABLE T (NAME VARCHAR(10),EMAIL VARCHAR(20));
INSERT INTO T VALUES('ABC','abc@gmail.com'),('ABCD','abcd@gmial.com');
ALTER TABLE T
ADD COLUMN ID INT FIRST;
UPDATE T JOIN(
SELECT NAME, ROW_NUMBER() OVER (ORDER BY NAME DESC) RN FROM T) s
ON S.NAME = T.NAME
SET T.ID = S.RN
;
ALTER TABLE T
MODIFY COLUMN ID INT AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM T;
Where id is added and then updated for existing records using row_number with order by
With this result
+----+------+----------------+
| ID | NAME | EMAIL |
+----+------+----------------+
| 1 | ABCD | abcd@gmial.com |
| 2 | ABC | abc@gmail.com |
+----+------+----------------+
2 rows in set (0.00 sec)
The second Alter statement then modifys id.
show create table t;
CREATE TABLE `t` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(10) DEFAULT NULL,
`EMAIL` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
If you are not on mysql version 8 or above you will need to replace the row_number bit with row number simulation using variables.
Not that it makes any difference anyway since any query would still need to include an order by to get the outcome you want.
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
Alter table does no allow you to order the new field so perhaps you need to do it in 2 stages. For example
DROP TABLE IF EXISTS T;
CREATE TABLE T (NAME VARCHAR(10),EMAIL VARCHAR(20));
INSERT INTO T VALUES('ABC','abc@gmail.com'),('ABCD','abcd@gmial.com');
ALTER TABLE T
ADD COLUMN ID INT FIRST;
UPDATE T JOIN(
SELECT NAME, ROW_NUMBER() OVER (ORDER BY NAME DESC) RN FROM T) s
ON S.NAME = T.NAME
SET T.ID = S.RN
;
ALTER TABLE T
MODIFY COLUMN ID INT AUTO_INCREMENT PRIMARY KEY;
SELECT * FROM T;
Where id is added and then updated for existing records using row_number with order by
With this result
+----+------+----------------+
| ID | NAME | EMAIL |
+----+------+----------------+
| 1 | ABCD | abcd@gmial.com |
| 2 | ABC | abc@gmail.com |
+----+------+----------------+
2 rows in set (0.00 sec)
The second Alter statement then modifys id.
show create table t;
CREATE TABLE `t` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(10) DEFAULT NULL,
`EMAIL` varchar(20) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
If you are not on mysql version 8 or above you will need to replace the row_number bit with row number simulation using variables.
Not that it makes any difference anyway since any query would still need to include an order by to get the outcome you want.
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
edited Jan 1 at 10:33
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
answered Jan 1 at 9:59
P.SalmonP.Salmon
7,9752415
7,9752415
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53994174%2fhow-to-add-auto-incremental-identity-column-to-table-with-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
add a proper data sample and the expected result
– scaisEdge
Jan 1 at 8:58