Mixing
Updating Duplicate records MySQL
Bellow is a screenshot of a table I have in my database, I want to insert a few products into that table but in the event a product already exists in the table I want it to update the quantity instead of inserting another row, so lets say I want to insert "Jelly Belly Harry Potter Jelly Slugs 2.1oz 12 CT" which already exists I would then want it to update the quantity from 2 to 3 and any products inserted that are not already in there to be added as a new row.
First time asking a question on here so I apologize if there is a mistake in my etiquette.
Table Screenshot
EDIT:
If you look at the Scan_id column that represents a customers order, so if i changed the last 3 items from scan_id 2 to 1 then those 3 items will be included in the other order, so there will be instances where I want duplicates in the name column as other orders that arent related may have that product on there, its when i want to merge orders that have the same products I just need to update the quantity.
mysql
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
|
show 2 more comments
Bellow is a screenshot of a table I have in my database, I want to insert a few products into that table but in the event a product already exists in the table I want it to update the quantity instead of inserting another row, so lets say I want to insert "Jelly Belly Harry Potter Jelly Slugs 2.1oz 12 CT" which already exists I would then want it to update the quantity from 2 to 3 and any products inserted that are not already in there to be added as a new row.
First time asking a question on here so I apologize if there is a mistake in my etiquette.
Table Screenshot
EDIT:
If you look at the Scan_id column that represents a customers order, so if i changed the last 3 items from scan_id 2 to 1 then those 3 items will be included in the other order, so there will be instances where I want duplicates in the name column as other orders that arent related may have that product on there, its when i want to merge orders that have the same products I just need to update the quantity.
mysql
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html and check the related topics to the right of your screen under the ads
– P.Salmon
Nov 22 '18 at 9:13
Welcome to Stack Overflow! Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 22 '18 at 9:16
I did come accross that in my search for answers, however from what I understand (which may be wrong as im rather new to mysql) It only updates if theres a "duplicate value in a UNIQUE index or PRIMARY KEY", which in this instance isnt the case as the name column isnt unique or a primary key? or am i missing something ?
– Mike Abineri
Nov 22 '18 at 9:16
@MikeAbineri it seems that name is going to be unique only for your case, so you should add the UNIQUE constraint on thenamecolumn
– Madhur Bhaiya
Nov 22 '18 at 9:17
@Mike Abineri How would you be able to detect a duplicate if it wasn't unique? Or is it that you don't want to create a unique key for some reason?
– P.Salmon
Nov 22 '18 at 9:25
|
show 2 more comments
Bellow is a screenshot of a table I have in my database, I want to insert a few products into that table but in the event a product already exists in the table I want it to update the quantity instead of inserting another row, so lets say I want to insert "Jelly Belly Harry Potter Jelly Slugs 2.1oz 12 CT" which already exists I would then want it to update the quantity from 2 to 3 and any products inserted that are not already in there to be added as a new row.
First time asking a question on here so I apologize if there is a mistake in my etiquette.
Table Screenshot
EDIT:
If you look at the Scan_id column that represents a customers order, so if i changed the last 3 items from scan_id 2 to 1 then those 3 items will be included in the other order, so there will be instances where I want duplicates in the name column as other orders that arent related may have that product on there, its when i want to merge orders that have the same products I just need to update the quantity.
mysql
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
Bellow is a screenshot of a table I have in my database, I want to insert a few products into that table but in the event a product already exists in the table I want it to update the quantity instead of inserting another row, so lets say I want to insert "Jelly Belly Harry Potter Jelly Slugs 2.1oz 12 CT" which already exists I would then want it to update the quantity from 2 to 3 and any products inserted that are not already in there to be added as a new row.
First time asking a question on here so I apologize if there is a mistake in my etiquette.
Table Screenshot
EDIT:
If you look at the Scan_id column that represents a customers order, so if i changed the last 3 items from scan_id 2 to 1 then those 3 items will be included in the other order, so there will be instances where I want duplicates in the name column as other orders that arent related may have that product on there, its when i want to merge orders that have the same products I just need to update the quantity.
mysql
mysql
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
edited Nov 22 '18 at 9:53
Mike Abineri
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
asked Nov 22 '18 at 9:11
Mike AbineriMike Abineri
526
526
dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html and check the related topics to the right of your screen under the ads
– P.Salmon
Nov 22 '18 at 9:13
Welcome to Stack Overflow! Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 22 '18 at 9:16
I did come accross that in my search for answers, however from what I understand (which may be wrong as im rather new to mysql) It only updates if theres a "duplicate value in a UNIQUE index or PRIMARY KEY", which in this instance isnt the case as the name column isnt unique or a primary key? or am i missing something ?
– Mike Abineri
Nov 22 '18 at 9:16
@MikeAbineri it seems that name is going to be unique only for your case, so you should add the UNIQUE constraint on thenamecolumn
– Madhur Bhaiya
Nov 22 '18 at 9:17
@Mike Abineri How would you be able to detect a duplicate if it wasn't unique? Or is it that you don't want to create a unique key for some reason?
– P.Salmon
Nov 22 '18 at 9:25
|
show 2 more comments
dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html and check the related topics to the right of your screen under the ads
– P.Salmon
Nov 22 '18 at 9:13
Welcome to Stack Overflow! Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 22 '18 at 9:16
I did come accross that in my search for answers, however from what I understand (which may be wrong as im rather new to mysql) It only updates if theres a "duplicate value in a UNIQUE index or PRIMARY KEY", which in this instance isnt the case as the name column isnt unique or a primary key? or am i missing something ?
– Mike Abineri
Nov 22 '18 at 9:16
@MikeAbineri it seems that name is going to be unique only for your case, so you should add the UNIQUE constraint on thenamecolumn
– Madhur Bhaiya
Nov 22 '18 at 9:17
@Mike Abineri How would you be able to detect a duplicate if it wasn't unique? Or is it that you don't want to create a unique key for some reason?
– P.Salmon
Nov 22 '18 at 9:25
dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html and check the related topics to the right of your screen under the ads
– P.Salmon
Nov 22 '18 at 9:13
dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html and check the related topics to the right of your screen under the ads
– P.Salmon
Nov 22 '18 at 9:13
Welcome to Stack Overflow! Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 22 '18 at 9:16
Welcome to Stack Overflow! Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 22 '18 at 9:16
I did come accross that in my search for answers, however from what I understand (which may be wrong as im rather new to mysql) It only updates if theres a "duplicate value in a UNIQUE index or PRIMARY KEY", which in this instance isnt the case as the name column isnt unique or a primary key? or am i missing something ?
– Mike Abineri
Nov 22 '18 at 9:16
I did come accross that in my search for answers, however from what I understand (which may be wrong as im rather new to mysql) It only updates if theres a "duplicate value in a UNIQUE index or PRIMARY KEY", which in this instance isnt the case as the name column isnt unique or a primary key? or am i missing something ?
– Mike Abineri
Nov 22 '18 at 9:16
@MikeAbineri it seems that name is going to be unique only for your case, so you should add the UNIQUE constraint on the
name column– Madhur Bhaiya
Nov 22 '18 at 9:17
@MikeAbineri it seems that name is going to be unique only for your case, so you should add the UNIQUE constraint on the
name column– Madhur Bhaiya
Nov 22 '18 at 9:17
@Mike Abineri How would you be able to detect a duplicate if it wasn't unique? Or is it that you don't want to create a unique key for some reason?
– P.Salmon
Nov 22 '18 at 9:25
@Mike Abineri How would you be able to detect a duplicate if it wasn't unique? Or is it that you don't want to create a unique key for some reason?
– P.Salmon
Nov 22 '18 at 9:25
|
show 2 more comments
1 Answer
1
active
oldest
votes
simply set the desired column to unique so as to prevent duplicates. What you is do an update instead of inserting the same values over and over again. I suggest you set the column to unique which means the values will be updated instead of being reinserted
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
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%2f53427358%2fupdating-duplicate-records-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
simply set the desired column to unique so as to prevent duplicates. What you is do an update instead of inserting the same values over and over again. I suggest you set the column to unique which means the values will be updated instead of being reinserted
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
add a comment |
simply set the desired column to unique so as to prevent duplicates. What you is do an update instead of inserting the same values over and over again. I suggest you set the column to unique which means the values will be updated instead of being reinserted
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
add a comment |
simply set the desired column to unique so as to prevent duplicates. What you is do an update instead of inserting the same values over and over again. I suggest you set the column to unique which means the values will be updated instead of being reinserted
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
simply set the desired column to unique so as to prevent duplicates. What you is do an update instead of inserting the same values over and over again. I suggest you set the column to unique which means the values will be updated instead of being reinserted
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
answered Nov 22 '18 at 9:18
ZidaneZidane
4442821
4442821
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%2f53427358%2fupdating-duplicate-records-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
dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html and check the related topics to the right of your screen under the ads
– P.Salmon
Nov 22 '18 at 9:13
Welcome to Stack Overflow! Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 22 '18 at 9:16
I did come accross that in my search for answers, however from what I understand (which may be wrong as im rather new to mysql) It only updates if theres a "duplicate value in a UNIQUE index or PRIMARY KEY", which in this instance isnt the case as the name column isnt unique or a primary key? or am i missing something ?
– Mike Abineri
Nov 22 '18 at 9:16
@MikeAbineri it seems that name is going to be unique only for your case, so you should add the UNIQUE constraint on the
namecolumn– Madhur Bhaiya
Nov 22 '18 at 9:17
@Mike Abineri How would you be able to detect a duplicate if it wasn't unique? Or is it that you don't want to create a unique key for some reason?
– P.Salmon
Nov 22 '18 at 9:25