Atomic update and backup ON DUPLICATE KEY insert else - golang sql statement
What is the best way to combine two statements (INSERT or (BACKUP and UPDATE)) and perform them atomically in golang?
I found this similar question:
https://codereview.stackexchange.com/questions/186909/query-select-and-insert-if-not-exists?newreg=067063956a834327883542c3171a22d4
But the Solution does there does not fulfil 2 of the the following requirements:
- perform an backup of the value ON DUPLICATE KEY,
- use standard SQL
- not use store procedures but
- remain atomic.
mysql sql
go asked Nov 20 '18 at 10:01
Macilias
731925
add a comment |
What is the best way to combine two statements (INSERT or (BACKUP and UPDATE)) and perform them atomically in golang?
I found this similar question:
https://codereview.stackexchange.com/questions/186909/query-select-and-insert-if-not-exists?newreg=067063956a834327883542c3171a22d4
But the Solution does there does not fulfil 2 of the the following requirements:
- perform an backup of the value ON DUPLICATE KEY,
- use standard SQL
- not use store procedures but
- remain atomic.
mysql sql
go asked Nov 20 '18 at 10:01
Macilias
731925
add a comment |
What is the best way to combine two statements (INSERT or (BACKUP and UPDATE)) and perform them atomically in golang?
I found this similar question:
https://codereview.stackexchange.com/questions/186909/query-select-and-insert-if-not-exists?newreg=067063956a834327883542c3171a22d4
But the Solution does there does not fulfil 2 of the the following requirements:
- perform an backup of the value ON DUPLICATE KEY,
- use standard SQL
- not use store procedures but
- remain atomic.
mysql sql
go asked Nov 20 '18 at 10:01
Macilias
731925
What is the best way to combine two statements (INSERT or (BACKUP and UPDATE)) and perform them atomically in golang?
I found this similar question:
https://codereview.stackexchange.com/questions/186909/query-select-and-insert-if-not-exists?newreg=067063956a834327883542c3171a22d4
But the Solution does there does not fulfil 2 of the the following requirements:
- perform an backup of the value ON DUPLICATE KEY,
- use standard SQL
- not use store procedures but
- remain atomic.
mysql sql
go mysql sql
go asked Nov 20 '18 at 10:01
Macilias
731925
asked Nov 20 '18 at 10:01
Macilias
731925
edited Nov 20 '18 at 10:34
asked Nov 20 '18 at 10:01
Macilias
731925
asked Nov 20 '18 at 10:01
Macilias
731925
asked Nov 20 '18 at 10:01
Macilias
731925
731925
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This is more a SQL question/answer than Go specific so the possible solutions are SQL based.
Possible solutions:
(1) REPLACE INTO
REPLACE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would overwrite an existing record. Works on unique constraint(s). Though when a matching record is found it will be deleted and thus it might a not wanted behavior.
(2) INSERT IGNORE
INSERT IGNORE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would add if the record does not exist. Works on unique constraint(s).
From the handbook:
If you use the IGNORE modifier, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row is discarded and no error occurs. Ignored errors may
generate warnings instead, although duplicate-key errors do not.
(3) INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ON DUPLICATE KEY UPDATE
title = 'No Green Eggs and Ham';
If the insert fails the values from ON DUPLICATE KEY will be used to make an update statement.
To do a backup create a history table ( a table with the same structure but amended with columns to get the change date ) and do a INSERT ... SELECT. To be atomic you would probable need to use transactions with the correct locking strategy - not sure how to get this right for MySQL.
Reference:
https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ for sample- https://dev.mysql.com/doc/refman/5.5/en/insert.html
- https://dev.mysql.com/doc/refman/5.5/en/replace.html
- https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
answered Nov 20 '18 at 10:49
Sascha
8,64033055
thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table.
– Macilias
Nov 20 '18 at 12:34
thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT
– Macilias
Nov 20 '18 at 12:49
ok according to stackoverflow.com/questions/3884344/… a stored procedure is the only way to achieve this.
– Macilias
Nov 20 '18 at 13:50
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%2f53390487%2fatomic-update-and-backup-on-duplicate-key-insert-else-golang-sql-statement%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
This is more a SQL question/answer than Go specific so the possible solutions are SQL based.
Possible solutions:
(1) REPLACE INTO
REPLACE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would overwrite an existing record. Works on unique constraint(s). Though when a matching record is found it will be deleted and thus it might a not wanted behavior.
(2) INSERT IGNORE
INSERT IGNORE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would add if the record does not exist. Works on unique constraint(s).
From the handbook:
If you use the IGNORE modifier, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row is discarded and no error occurs. Ignored errors may
generate warnings instead, although duplicate-key errors do not.
(3) INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ON DUPLICATE KEY UPDATE
title = 'No Green Eggs and Ham';
If the insert fails the values from ON DUPLICATE KEY will be used to make an update statement.
To do a backup create a history table ( a table with the same structure but amended with columns to get the change date ) and do a INSERT ... SELECT. To be atomic you would probable need to use transactions with the correct locking strategy - not sure how to get this right for MySQL.
Reference:
https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ for sample- https://dev.mysql.com/doc/refman/5.5/en/insert.html
- https://dev.mysql.com/doc/refman/5.5/en/replace.html
- https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
answered Nov 20 '18 at 10:49
Sascha
8,64033055
thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table.
– Macilias
Nov 20 '18 at 12:34
thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT
– Macilias
Nov 20 '18 at 12:49
ok according to stackoverflow.com/questions/3884344/… a stored procedure is the only way to achieve this.
– Macilias
Nov 20 '18 at 13:50
add a comment |
This is more a SQL question/answer than Go specific so the possible solutions are SQL based.
Possible solutions:
(1) REPLACE INTO
REPLACE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would overwrite an existing record. Works on unique constraint(s). Though when a matching record is found it will be deleted and thus it might a not wanted behavior.
(2) INSERT IGNORE
INSERT IGNORE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would add if the record does not exist. Works on unique constraint(s).
From the handbook:
If you use the IGNORE modifier, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row is discarded and no error occurs. Ignored errors may
generate warnings instead, although duplicate-key errors do not.
(3) INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ON DUPLICATE KEY UPDATE
title = 'No Green Eggs and Ham';
If the insert fails the values from ON DUPLICATE KEY will be used to make an update statement.
To do a backup create a history table ( a table with the same structure but amended with columns to get the change date ) and do a INSERT ... SELECT. To be atomic you would probable need to use transactions with the correct locking strategy - not sure how to get this right for MySQL.
Reference:
https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ for sample- https://dev.mysql.com/doc/refman/5.5/en/insert.html
- https://dev.mysql.com/doc/refman/5.5/en/replace.html
- https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
answered Nov 20 '18 at 10:49
Sascha
8,64033055
thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table.
– Macilias
Nov 20 '18 at 12:34
thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT
– Macilias
Nov 20 '18 at 12:49
ok according to stackoverflow.com/questions/3884344/… a stored procedure is the only way to achieve this.
– Macilias
Nov 20 '18 at 13:50
add a comment |
This is more a SQL question/answer than Go specific so the possible solutions are SQL based.
Possible solutions:
(1) REPLACE INTO
REPLACE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would overwrite an existing record. Works on unique constraint(s). Though when a matching record is found it will be deleted and thus it might a not wanted behavior.
(2) INSERT IGNORE
INSERT IGNORE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would add if the record does not exist. Works on unique constraint(s).
From the handbook:
If you use the IGNORE modifier, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row is discarded and no error occurs. Ignored errors may
generate warnings instead, although duplicate-key errors do not.
(3) INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ON DUPLICATE KEY UPDATE
title = 'No Green Eggs and Ham';
If the insert fails the values from ON DUPLICATE KEY will be used to make an update statement.
To do a backup create a history table ( a table with the same structure but amended with columns to get the change date ) and do a INSERT ... SELECT. To be atomic you would probable need to use transactions with the correct locking strategy - not sure how to get this right for MySQL.
Reference:
https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ for sample- https://dev.mysql.com/doc/refman/5.5/en/insert.html
- https://dev.mysql.com/doc/refman/5.5/en/replace.html
- https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
answered Nov 20 '18 at 10:49
Sascha
8,64033055
This is more a SQL question/answer than Go specific so the possible solutions are SQL based.
Possible solutions:
(1) REPLACE INTO
REPLACE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would overwrite an existing record. Works on unique constraint(s). Though when a matching record is found it will be deleted and thus it might a not wanted behavior.
(2) INSERT IGNORE
INSERT IGNORE INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
This would add if the record does not exist. Works on unique constraint(s).
From the handbook:
If you use the IGNORE modifier, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row is discarded and no error occurs. Ignored errors may
generate warnings instead, although duplicate-key errors do not.
(3) INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO books
(id, title, author, year_published)
VALUES
(1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);
ON DUPLICATE KEY UPDATE
title = 'No Green Eggs and Ham';
If the insert fails the values from ON DUPLICATE KEY will be used to make an update statement.
To do a backup create a history table ( a table with the same structure but amended with columns to get the change date ) and do a INSERT ... SELECT. To be atomic you would probable need to use transactions with the correct locking strategy - not sure how to get this right for MySQL.
Reference:
https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/ for sample- https://dev.mysql.com/doc/refman/5.5/en/insert.html
- https://dev.mysql.com/doc/refman/5.5/en/replace.html
- https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
answered Nov 20 '18 at 10:49
Sascha
8,64033055
edited Nov 20 '18 at 10:55
answered Nov 20 '18 at 10:49
Sascha
8,64033055
answered Nov 20 '18 at 10:49
Sascha
8,64033055
answered Nov 20 '18 at 10:49
Sascha
8,64033055
8,64033055
thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table.
– Macilias
Nov 20 '18 at 12:34
thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT
– Macilias
Nov 20 '18 at 12:49
ok according to stackoverflow.com/questions/3884344/… a stored procedure is the only way to achieve this.
– Macilias
Nov 20 '18 at 13:50
add a comment |
thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table.
– Macilias
Nov 20 '18 at 12:34
thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT
– Macilias
Nov 20 '18 at 12:49
ok according to stackoverflow.com/questions/3884344/… a stored procedure is the only way to achieve this.
– Macilias
Nov 20 '18 at 13:50
thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table.
– Macilias
Nov 20 '18 at 12:34
thx and it might be that what I ask for is unpossible, but I wanted to react on duplicate-key error from within the sql statement and not only update the row but also copy the old values to a backup table.
– Macilias
Nov 20 '18 at 12:34
thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT
– Macilias
Nov 20 '18 at 12:49
thats why only (3) comes close to be an answer, but violates the standard sql requirement. Obviously DB manipulations has not been planed properly for the standard sql specification, since MySQL and Postgres has different solutions for this common case: ON DUPLICATE KEY and UPSERT
– Macilias
Nov 20 '18 at 12:49
ok according to stackoverflow.com/questions/3884344/… a stored procedure is the only way to achieve this.
– Macilias
Nov 20 '18 at 13:50
ok according to stackoverflow.com/questions/3884344/… a stored procedure is the only way to achieve this.
– Macilias
Nov 20 '18 at 13:50
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53390487%2fatomic-update-and-backup-on-duplicate-key-insert-else-golang-sql-statement%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
