How to use auto-increment Id in another column in same INSERT QUERY in MYSQL?
How to use auto-increment Id in another column in INSERT QUERY in MYSQL?
I want to add user with code. like if user_id after insert is 1 the code will be US1 similarly if user_id after insert is 954 the code will be US954
This is what I'm currently doing
"INSERT INTO `users` (`user_id`, `user`, `code`) VALUES (NULL, 'Alice', NULL);"
and then retrieve last insert id in PHP (Codeigniter)
$insert_id = $this->db->insert_id();
and then update code with auto increment id
"UPDATE `users` SET `code` = CONCAT('US', '" . $insert_id . "') WHERE `user_id` = " . $insert_id . ";";
Is there anyway you can do it in a single INSERT QUERY in MYSQL?
EDIT:
This is not same as this Insert/ update at the same time in a MySql table?. Because this one asking on concurrent operation of two users simultaneously which is not my question
php mysql
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
|
show 4 more comments
How to use auto-increment Id in another column in INSERT QUERY in MYSQL?
I want to add user with code. like if user_id after insert is 1 the code will be US1 similarly if user_id after insert is 954 the code will be US954
This is what I'm currently doing
"INSERT INTO `users` (`user_id`, `user`, `code`) VALUES (NULL, 'Alice', NULL);"
and then retrieve last insert id in PHP (Codeigniter)
$insert_id = $this->db->insert_id();
and then update code with auto increment id
"UPDATE `users` SET `code` = CONCAT('US', '" . $insert_id . "') WHERE `user_id` = " . $insert_id . ";";
Is there anyway you can do it in a single INSERT QUERY in MYSQL?
EDIT:
This is not same as this Insert/ update at the same time in a MySql table?. Because this one asking on concurrent operation of two users simultaneously which is not my question
php mysql
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
2
No, think about it, you asking if it's possible to get the egg before the chicken has laid it!
– SPlatten
Nov 14 '18 at 11:25
2
"you asking if it's possible to get the egg before the chicken has laid it!" What makes me wonder was the chicken first or the egg? @SPlatten
– Raymond Nijland
Nov 14 '18 at 11:28
@RaymondNijland lol I get it now.
– Faizan Rupani
Nov 14 '18 at 11:30
1
@RaymondNijland, :) In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID...I would suggest writing a stored procedure which enables you to manage this kind of thing much easier.
– SPlatten
Nov 14 '18 at 12:50
1
"In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID." i know @SPlatten i did post and removed a answer aboutLAST_INSERT_ID().. because it didn't know for sure if the topicstarter required that and because it wanted to do it in one query.
– Raymond Nijland
Nov 14 '18 at 16:49
|
show 4 more comments
How to use auto-increment Id in another column in INSERT QUERY in MYSQL?
I want to add user with code. like if user_id after insert is 1 the code will be US1 similarly if user_id after insert is 954 the code will be US954
This is what I'm currently doing
"INSERT INTO `users` (`user_id`, `user`, `code`) VALUES (NULL, 'Alice', NULL);"
and then retrieve last insert id in PHP (Codeigniter)
$insert_id = $this->db->insert_id();
and then update code with auto increment id
"UPDATE `users` SET `code` = CONCAT('US', '" . $insert_id . "') WHERE `user_id` = " . $insert_id . ";";
Is there anyway you can do it in a single INSERT QUERY in MYSQL?
EDIT:
This is not same as this Insert/ update at the same time in a MySql table?. Because this one asking on concurrent operation of two users simultaneously which is not my question
php mysql
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
How to use auto-increment Id in another column in INSERT QUERY in MYSQL?
I want to add user with code. like if user_id after insert is 1 the code will be US1 similarly if user_id after insert is 954 the code will be US954
This is what I'm currently doing
"INSERT INTO `users` (`user_id`, `user`, `code`) VALUES (NULL, 'Alice', NULL);"
and then retrieve last insert id in PHP (Codeigniter)
$insert_id = $this->db->insert_id();
and then update code with auto increment id
"UPDATE `users` SET `code` = CONCAT('US', '" . $insert_id . "') WHERE `user_id` = " . $insert_id . ";";
Is there anyway you can do it in a single INSERT QUERY in MYSQL?
EDIT:
This is not same as this Insert/ update at the same time in a MySql table?. Because this one asking on concurrent operation of two users simultaneously which is not my question
php mysql
php mysql
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
edited Nov 14 '18 at 11:30
Faizan Rupani
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
asked Nov 14 '18 at 11:22
Faizan RupaniFaizan Rupani
320110
320110
2
No, think about it, you asking if it's possible to get the egg before the chicken has laid it!
– SPlatten
Nov 14 '18 at 11:25
2
"you asking if it's possible to get the egg before the chicken has laid it!" What makes me wonder was the chicken first or the egg? @SPlatten
– Raymond Nijland
Nov 14 '18 at 11:28
@RaymondNijland lol I get it now.
– Faizan Rupani
Nov 14 '18 at 11:30
1
@RaymondNijland, :) In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID...I would suggest writing a stored procedure which enables you to manage this kind of thing much easier.
– SPlatten
Nov 14 '18 at 12:50
1
"In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID." i know @SPlatten i did post and removed a answer aboutLAST_INSERT_ID().. because it didn't know for sure if the topicstarter required that and because it wanted to do it in one query.
– Raymond Nijland
Nov 14 '18 at 16:49
|
show 4 more comments
2
No, think about it, you asking if it's possible to get the egg before the chicken has laid it!
– SPlatten
Nov 14 '18 at 11:25
2
"you asking if it's possible to get the egg before the chicken has laid it!" What makes me wonder was the chicken first or the egg? @SPlatten
– Raymond Nijland
Nov 14 '18 at 11:28
@RaymondNijland lol I get it now.
– Faizan Rupani
Nov 14 '18 at 11:30
1
@RaymondNijland, :) In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID...I would suggest writing a stored procedure which enables you to manage this kind of thing much easier.
– SPlatten
Nov 14 '18 at 12:50
1
"In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID." i know @SPlatten i did post and removed a answer aboutLAST_INSERT_ID().. because it didn't know for sure if the topicstarter required that and because it wanted to do it in one query.
– Raymond Nijland
Nov 14 '18 at 16:49
2
2
No, think about it, you asking if it's possible to get the egg before the chicken has laid it!
– SPlatten
Nov 14 '18 at 11:25
No, think about it, you asking if it's possible to get the egg before the chicken has laid it!
– SPlatten
Nov 14 '18 at 11:25
2
2
"you asking if it's possible to get the egg before the chicken has laid it!" What makes me wonder was the chicken first or the egg? @SPlatten
– Raymond Nijland
Nov 14 '18 at 11:28
"you asking if it's possible to get the egg before the chicken has laid it!" What makes me wonder was the chicken first or the egg? @SPlatten
– Raymond Nijland
Nov 14 '18 at 11:28
@RaymondNijland lol I get it now.
– Faizan Rupani
Nov 14 '18 at 11:30
@RaymondNijland lol I get it now.
– Faizan Rupani
Nov 14 '18 at 11:30
1
1
@RaymondNijland, :) In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID...I would suggest writing a stored procedure which enables you to manage this kind of thing much easier.
– SPlatten
Nov 14 '18 at 12:50
@RaymondNijland, :) In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID...I would suggest writing a stored procedure which enables you to manage this kind of thing much easier.
– SPlatten
Nov 14 '18 at 12:50
1
1
"In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID." i know @SPlatten i did post and removed a answer about
LAST_INSERT_ID() .. because it didn't know for sure if the topicstarter required that and because it wanted to do it in one query.– Raymond Nijland
Nov 14 '18 at 16:49
"In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID." i know @SPlatten i did post and removed a answer about
LAST_INSERT_ID() .. because it didn't know for sure if the topicstarter required that and because it wanted to do it in one query.– Raymond Nijland
Nov 14 '18 at 16:49
|
show 4 more comments
3 Answers
3
active
oldest
votes
Yes, it's possible.
You can do it using Store Procedure(SP) in MySQL.
You have to call SP one time for each registration instead of twice query(which you did).
It will save lot execution time.
https://stackoverflow.com/questions/14957788/insert-and-update-with-stored-procedure
https://www.w3resource.com/mysql/mysql-procedure.php
https://www.sitepoint.com/stored-procedures-mysql-php/
http://php.net/manual/en/mysqli.quickstart.stored-procedures.php
Please use above links for more information.
I recommend to use SP for Login/Registration. It will give extra power to your PHP web app.
For your basic example. I've shared similar SP code with you.
BEGIN
declare autoIncr int;
//insert code
autoIncr = LAST_INSERT_ID();
//update code
END
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
that doesn't give me any solid answer. I don't know what to look for in your answer
– Faizan Rupani
Nov 14 '18 at 11:47
Your questions is, you have to achieve using single query. Same, thing you can achieve using single SP call instead of two different call of query.
– Darshak DRC
Nov 14 '18 at 11:49
If I'm inserting and updating separately in SP, then there is no point of using it. because I can run insert and update query separately in code. And it will take less time as compared to SP.
– Faizan Rupani
Nov 14 '18 at 11:50
SP is take less time. If you will call insert and update query then every time call database server. But in SP, php call database server only time and database server can execute query in single database call instead of twice call of database.
– Darshak DRC
Nov 14 '18 at 11:53
To use SP you would have to access to SP permission from hosting provider. Some Hosting Providers doesn't allow it. unless you own your server. And doing insert and update query only makes difference negligible.
– Faizan Rupani
Nov 14 '18 at 11:58
|
show 1 more comment
Get Newly user_id with MAX(user_id)+1 and then concat it with your string
INSERT INTO `users` (`user`, `code`) SELECT 'Alice',CONCAT("US",MAX(user_id)+1) from users
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
If for example more than 300 people insert simultaneously then you'll have different max user_id for each user. Beside you won't get an actual user_id with MAX(user_id) until you insert the data. otherwise it'll give you last update id
– Faizan Rupani
Nov 14 '18 at 13:38
Plz elaborate with your code
– Komal G.
Nov 14 '18 at 13:49
add a comment |
when you created your table and column that time if you given the value of user_id Auto increment. After that when you want to insert the data no need to give value of user_id cause it's will auto incremented
for the create fresh create new table query
CREATE TABLE `users` (
ID int NOT NULL AUTO_INCREMENT,
user_id varchar(255) NOT NULL AUTO_INCREMENT,
user varchar(255),
code text,
PRIMARY KEY (ID)
);
that is the inserting query
"INSERT INTO `users` (`user`, `code`) VALUES ('Alice', NULL);"
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
I know. That was sample data. And that's not what I asked
– Faizan Rupani
Nov 14 '18 at 11:33
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%2f53299069%2fhow-to-use-auto-increment-id-in-another-column-in-same-insert-query-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Yes, it's possible.
You can do it using Store Procedure(SP) in MySQL.
You have to call SP one time for each registration instead of twice query(which you did).
It will save lot execution time.
https://stackoverflow.com/questions/14957788/insert-and-update-with-stored-procedure
https://www.w3resource.com/mysql/mysql-procedure.php
https://www.sitepoint.com/stored-procedures-mysql-php/
http://php.net/manual/en/mysqli.quickstart.stored-procedures.php
Please use above links for more information.
I recommend to use SP for Login/Registration. It will give extra power to your PHP web app.
For your basic example. I've shared similar SP code with you.
BEGIN
declare autoIncr int;
//insert code
autoIncr = LAST_INSERT_ID();
//update code
END
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
that doesn't give me any solid answer. I don't know what to look for in your answer
– Faizan Rupani
Nov 14 '18 at 11:47
Your questions is, you have to achieve using single query. Same, thing you can achieve using single SP call instead of two different call of query.
– Darshak DRC
Nov 14 '18 at 11:49
If I'm inserting and updating separately in SP, then there is no point of using it. because I can run insert and update query separately in code. And it will take less time as compared to SP.
– Faizan Rupani
Nov 14 '18 at 11:50
SP is take less time. If you will call insert and update query then every time call database server. But in SP, php call database server only time and database server can execute query in single database call instead of twice call of database.
– Darshak DRC
Nov 14 '18 at 11:53
To use SP you would have to access to SP permission from hosting provider. Some Hosting Providers doesn't allow it. unless you own your server. And doing insert and update query only makes difference negligible.
– Faizan Rupani
Nov 14 '18 at 11:58
|
show 1 more comment
Yes, it's possible.
You can do it using Store Procedure(SP) in MySQL.
You have to call SP one time for each registration instead of twice query(which you did).
It will save lot execution time.
https://stackoverflow.com/questions/14957788/insert-and-update-with-stored-procedure
https://www.w3resource.com/mysql/mysql-procedure.php
https://www.sitepoint.com/stored-procedures-mysql-php/
http://php.net/manual/en/mysqli.quickstart.stored-procedures.php
Please use above links for more information.
I recommend to use SP for Login/Registration. It will give extra power to your PHP web app.
For your basic example. I've shared similar SP code with you.
BEGIN
declare autoIncr int;
//insert code
autoIncr = LAST_INSERT_ID();
//update code
END
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
that doesn't give me any solid answer. I don't know what to look for in your answer
– Faizan Rupani
Nov 14 '18 at 11:47
Your questions is, you have to achieve using single query. Same, thing you can achieve using single SP call instead of two different call of query.
– Darshak DRC
Nov 14 '18 at 11:49
If I'm inserting and updating separately in SP, then there is no point of using it. because I can run insert and update query separately in code. And it will take less time as compared to SP.
– Faizan Rupani
Nov 14 '18 at 11:50
SP is take less time. If you will call insert and update query then every time call database server. But in SP, php call database server only time and database server can execute query in single database call instead of twice call of database.
– Darshak DRC
Nov 14 '18 at 11:53
To use SP you would have to access to SP permission from hosting provider. Some Hosting Providers doesn't allow it. unless you own your server. And doing insert and update query only makes difference negligible.
– Faizan Rupani
Nov 14 '18 at 11:58
|
show 1 more comment
Yes, it's possible.
You can do it using Store Procedure(SP) in MySQL.
You have to call SP one time for each registration instead of twice query(which you did).
It will save lot execution time.
https://stackoverflow.com/questions/14957788/insert-and-update-with-stored-procedure
https://www.w3resource.com/mysql/mysql-procedure.php
https://www.sitepoint.com/stored-procedures-mysql-php/
http://php.net/manual/en/mysqli.quickstart.stored-procedures.php
Please use above links for more information.
I recommend to use SP for Login/Registration. It will give extra power to your PHP web app.
For your basic example. I've shared similar SP code with you.
BEGIN
declare autoIncr int;
//insert code
autoIncr = LAST_INSERT_ID();
//update code
END
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
Yes, it's possible.
You can do it using Store Procedure(SP) in MySQL.
You have to call SP one time for each registration instead of twice query(which you did).
It will save lot execution time.
https://stackoverflow.com/questions/14957788/insert-and-update-with-stored-procedure
https://www.w3resource.com/mysql/mysql-procedure.php
https://www.sitepoint.com/stored-procedures-mysql-php/
http://php.net/manual/en/mysqli.quickstart.stored-procedures.php
Please use above links for more information.
I recommend to use SP for Login/Registration. It will give extra power to your PHP web app.
For your basic example. I've shared similar SP code with you.
BEGIN
declare autoIncr int;
//insert code
autoIncr = LAST_INSERT_ID();
//update code
END
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
edited Nov 14 '18 at 11:42
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
answered Nov 14 '18 at 11:36
Darshak DRCDarshak DRC
56
56
that doesn't give me any solid answer. I don't know what to look for in your answer
– Faizan Rupani
Nov 14 '18 at 11:47
Your questions is, you have to achieve using single query. Same, thing you can achieve using single SP call instead of two different call of query.
– Darshak DRC
Nov 14 '18 at 11:49
If I'm inserting and updating separately in SP, then there is no point of using it. because I can run insert and update query separately in code. And it will take less time as compared to SP.
– Faizan Rupani
Nov 14 '18 at 11:50
SP is take less time. If you will call insert and update query then every time call database server. But in SP, php call database server only time and database server can execute query in single database call instead of twice call of database.
– Darshak DRC
Nov 14 '18 at 11:53
To use SP you would have to access to SP permission from hosting provider. Some Hosting Providers doesn't allow it. unless you own your server. And doing insert and update query only makes difference negligible.
– Faizan Rupani
Nov 14 '18 at 11:58
|
show 1 more comment
that doesn't give me any solid answer. I don't know what to look for in your answer
– Faizan Rupani
Nov 14 '18 at 11:47
Your questions is, you have to achieve using single query. Same, thing you can achieve using single SP call instead of two different call of query.
– Darshak DRC
Nov 14 '18 at 11:49
If I'm inserting and updating separately in SP, then there is no point of using it. because I can run insert and update query separately in code. And it will take less time as compared to SP.
– Faizan Rupani
Nov 14 '18 at 11:50
SP is take less time. If you will call insert and update query then every time call database server. But in SP, php call database server only time and database server can execute query in single database call instead of twice call of database.
– Darshak DRC
Nov 14 '18 at 11:53
To use SP you would have to access to SP permission from hosting provider. Some Hosting Providers doesn't allow it. unless you own your server. And doing insert and update query only makes difference negligible.
– Faizan Rupani
Nov 14 '18 at 11:58
that doesn't give me any solid answer. I don't know what to look for in your answer
– Faizan Rupani
Nov 14 '18 at 11:47
that doesn't give me any solid answer. I don't know what to look for in your answer
– Faizan Rupani
Nov 14 '18 at 11:47
Your questions is, you have to achieve using single query. Same, thing you can achieve using single SP call instead of two different call of query.
– Darshak DRC
Nov 14 '18 at 11:49
Your questions is, you have to achieve using single query. Same, thing you can achieve using single SP call instead of two different call of query.
– Darshak DRC
Nov 14 '18 at 11:49
If I'm inserting and updating separately in SP, then there is no point of using it. because I can run insert and update query separately in code. And it will take less time as compared to SP.
– Faizan Rupani
Nov 14 '18 at 11:50
If I'm inserting and updating separately in SP, then there is no point of using it. because I can run insert and update query separately in code. And it will take less time as compared to SP.
– Faizan Rupani
Nov 14 '18 at 11:50
SP is take less time. If you will call insert and update query then every time call database server. But in SP, php call database server only time and database server can execute query in single database call instead of twice call of database.
– Darshak DRC
Nov 14 '18 at 11:53
SP is take less time. If you will call insert and update query then every time call database server. But in SP, php call database server only time and database server can execute query in single database call instead of twice call of database.
– Darshak DRC
Nov 14 '18 at 11:53
To use SP you would have to access to SP permission from hosting provider. Some Hosting Providers doesn't allow it. unless you own your server. And doing insert and update query only makes difference negligible.
– Faizan Rupani
Nov 14 '18 at 11:58
To use SP you would have to access to SP permission from hosting provider. Some Hosting Providers doesn't allow it. unless you own your server. And doing insert and update query only makes difference negligible.
– Faizan Rupani
Nov 14 '18 at 11:58
|
show 1 more comment
Get Newly user_id with MAX(user_id)+1 and then concat it with your string
INSERT INTO `users` (`user`, `code`) SELECT 'Alice',CONCAT("US",MAX(user_id)+1) from users
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
If for example more than 300 people insert simultaneously then you'll have different max user_id for each user. Beside you won't get an actual user_id with MAX(user_id) until you insert the data. otherwise it'll give you last update id
– Faizan Rupani
Nov 14 '18 at 13:38
Plz elaborate with your code
– Komal G.
Nov 14 '18 at 13:49
add a comment |
Get Newly user_id with MAX(user_id)+1 and then concat it with your string
INSERT INTO `users` (`user`, `code`) SELECT 'Alice',CONCAT("US",MAX(user_id)+1) from users
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
If for example more than 300 people insert simultaneously then you'll have different max user_id for each user. Beside you won't get an actual user_id with MAX(user_id) until you insert the data. otherwise it'll give you last update id
– Faizan Rupani
Nov 14 '18 at 13:38
Plz elaborate with your code
– Komal G.
Nov 14 '18 at 13:49
add a comment |
Get Newly user_id with MAX(user_id)+1 and then concat it with your string
INSERT INTO `users` (`user`, `code`) SELECT 'Alice',CONCAT("US",MAX(user_id)+1) from users
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
Get Newly user_id with MAX(user_id)+1 and then concat it with your string
INSERT INTO `users` (`user`, `code`) SELECT 'Alice',CONCAT("US",MAX(user_id)+1) from users
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
answered Nov 14 '18 at 13:07
Komal G.Komal G.
315112
315112
If for example more than 300 people insert simultaneously then you'll have different max user_id for each user. Beside you won't get an actual user_id with MAX(user_id) until you insert the data. otherwise it'll give you last update id
– Faizan Rupani
Nov 14 '18 at 13:38
Plz elaborate with your code
– Komal G.
Nov 14 '18 at 13:49
add a comment |
If for example more than 300 people insert simultaneously then you'll have different max user_id for each user. Beside you won't get an actual user_id with MAX(user_id) until you insert the data. otherwise it'll give you last update id
– Faizan Rupani
Nov 14 '18 at 13:38
Plz elaborate with your code
– Komal G.
Nov 14 '18 at 13:49
If for example more than 300 people insert simultaneously then you'll have different max user_id for each user. Beside you won't get an actual user_id with MAX(user_id) until you insert the data. otherwise it'll give you last update id
– Faizan Rupani
Nov 14 '18 at 13:38
If for example more than 300 people insert simultaneously then you'll have different max user_id for each user. Beside you won't get an actual user_id with MAX(user_id) until you insert the data. otherwise it'll give you last update id
– Faizan Rupani
Nov 14 '18 at 13:38
Plz elaborate with your code
– Komal G.
Nov 14 '18 at 13:49
Plz elaborate with your code
– Komal G.
Nov 14 '18 at 13:49
add a comment |
when you created your table and column that time if you given the value of user_id Auto increment. After that when you want to insert the data no need to give value of user_id cause it's will auto incremented
for the create fresh create new table query
CREATE TABLE `users` (
ID int NOT NULL AUTO_INCREMENT,
user_id varchar(255) NOT NULL AUTO_INCREMENT,
user varchar(255),
code text,
PRIMARY KEY (ID)
);
that is the inserting query
"INSERT INTO `users` (`user`, `code`) VALUES ('Alice', NULL);"
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
I know. That was sample data. And that's not what I asked
– Faizan Rupani
Nov 14 '18 at 11:33
add a comment |
when you created your table and column that time if you given the value of user_id Auto increment. After that when you want to insert the data no need to give value of user_id cause it's will auto incremented
for the create fresh create new table query
CREATE TABLE `users` (
ID int NOT NULL AUTO_INCREMENT,
user_id varchar(255) NOT NULL AUTO_INCREMENT,
user varchar(255),
code text,
PRIMARY KEY (ID)
);
that is the inserting query
"INSERT INTO `users` (`user`, `code`) VALUES ('Alice', NULL);"
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
I know. That was sample data. And that's not what I asked
– Faizan Rupani
Nov 14 '18 at 11:33
add a comment |
when you created your table and column that time if you given the value of user_id Auto increment. After that when you want to insert the data no need to give value of user_id cause it's will auto incremented
for the create fresh create new table query
CREATE TABLE `users` (
ID int NOT NULL AUTO_INCREMENT,
user_id varchar(255) NOT NULL AUTO_INCREMENT,
user varchar(255),
code text,
PRIMARY KEY (ID)
);
that is the inserting query
"INSERT INTO `users` (`user`, `code`) VALUES ('Alice', NULL);"
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
when you created your table and column that time if you given the value of user_id Auto increment. After that when you want to insert the data no need to give value of user_id cause it's will auto incremented
for the create fresh create new table query
CREATE TABLE `users` (
ID int NOT NULL AUTO_INCREMENT,
user_id varchar(255) NOT NULL AUTO_INCREMENT,
user varchar(255),
code text,
PRIMARY KEY (ID)
);
that is the inserting query
"INSERT INTO `users` (`user`, `code`) VALUES ('Alice', NULL);"
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
edited Nov 14 '18 at 11:34
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
answered Nov 14 '18 at 11:32
Istiyak AminIstiyak Amin
15312
15312
I know. That was sample data. And that's not what I asked
– Faizan Rupani
Nov 14 '18 at 11:33
add a comment |
I know. That was sample data. And that's not what I asked
– Faizan Rupani
Nov 14 '18 at 11:33
I know. That was sample data. And that's not what I asked
– Faizan Rupani
Nov 14 '18 at 11:33
I know. That was sample data. And that's not what I asked
– Faizan Rupani
Nov 14 '18 at 11:33
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%2f53299069%2fhow-to-use-auto-increment-id-in-another-column-in-same-insert-query-in-mysql%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
2
No, think about it, you asking if it's possible to get the egg before the chicken has laid it!
– SPlatten
Nov 14 '18 at 11:25
2
"you asking if it's possible to get the egg before the chicken has laid it!" What makes me wonder was the chicken first or the egg? @SPlatten
– Raymond Nijland
Nov 14 '18 at 11:28
@RaymondNijland lol I get it now.
– Faizan Rupani
Nov 14 '18 at 11:30
1
@RaymondNijland, :) In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID...I would suggest writing a stored procedure which enables you to manage this kind of thing much easier.
– SPlatten
Nov 14 '18 at 12:50
1
"In this case you can get the last insert ID once the record has been inserted, but what is required here is to use the same ID." i know @SPlatten i did post and removed a answer about
LAST_INSERT_ID().. because it didn't know for sure if the topicstarter required that and because it wanted to do it in one query.– Raymond Nijland
Nov 14 '18 at 16:49