How to copy a row from one mysql table to another
Please forgive if already answered. I have looked at lots of other questions and none are helping! Basically, I want to copy a single row from one table, to another table. The problem I have struck, is the the id of the row I want to copy, is already in use, in the table I want to copy to. This is what I have at present, it works, until it strikes this problem.
INSERT INTO venues SELECT * FROM submitted_venues WHERE id='9';
It generates the error: Duplicate entry '9' for key 'id'
Is there a way to change the id number on the fly, so that it create an unused new number for the receiving table, or do I have to write a query that names and gets all of the fields for each table. Some thing like the replace statement 0 cases would be good, where 0 creates a new record!!
The 9, is just a number that I used for this example, of course it changes.
EDIT:
I have created a work around for this problem.
//get highest id number of table to copy to
$sql = "SELECT id FROM venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$to_id = $row[ 0 ];
//increase id
$to_id ++;
//get highest id number of table to copy from
$sql = "SELECT id FROM submitted_venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$from_id = $row[ 0 ];
//increase id
$from_id ++;
//get highest unused id number
$temp_id = $to_id > $from_id ? $to_id : $from_id;
//update existing record to ensure that its id number will not conflict
$sql = "UPDATE submitted_venues SET id = '$temp_id' WHERE id='$current_venue_id'";
if( $result = mysqli_query( $mysqli, $sql ) ){
$current_venue_id = $temp_id;
//copy record to normal regular venues table
$sql = "INSERT INTO venues SELECT * FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not copy -> " . mysqli_error($mysqli) );
//delete record from temp db table
$sql = "DELETE FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not delete -> " . mysqli_error($mysqli) );
}
}
}
mysql copy row
asked Nov 25 '18 at 9:12
ZastZast
2401318
add a comment |
Please forgive if already answered. I have looked at lots of other questions and none are helping! Basically, I want to copy a single row from one table, to another table. The problem I have struck, is the the id of the row I want to copy, is already in use, in the table I want to copy to. This is what I have at present, it works, until it strikes this problem.
INSERT INTO venues SELECT * FROM submitted_venues WHERE id='9';
It generates the error: Duplicate entry '9' for key 'id'
Is there a way to change the id number on the fly, so that it create an unused new number for the receiving table, or do I have to write a query that names and gets all of the fields for each table. Some thing like the replace statement 0 cases would be good, where 0 creates a new record!!
The 9, is just a number that I used for this example, of course it changes.
EDIT:
I have created a work around for this problem.
//get highest id number of table to copy to
$sql = "SELECT id FROM venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$to_id = $row[ 0 ];
//increase id
$to_id ++;
//get highest id number of table to copy from
$sql = "SELECT id FROM submitted_venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$from_id = $row[ 0 ];
//increase id
$from_id ++;
//get highest unused id number
$temp_id = $to_id > $from_id ? $to_id : $from_id;
//update existing record to ensure that its id number will not conflict
$sql = "UPDATE submitted_venues SET id = '$temp_id' WHERE id='$current_venue_id'";
if( $result = mysqli_query( $mysqli, $sql ) ){
$current_venue_id = $temp_id;
//copy record to normal regular venues table
$sql = "INSERT INTO venues SELECT * FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not copy -> " . mysqli_error($mysqli) );
//delete record from temp db table
$sql = "DELETE FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not delete -> " . mysqli_error($mysqli) );
}
}
}
mysql copy row
asked Nov 25 '18 at 9:12
ZastZast
2401318
Take a look at ‘ON DUPLICATE KEY’ dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
– Adam Brinded
Nov 25 '18 at 9:15
There is no syntax that allows you to exclude one column from the asterix wilcard. You can add calclulated columns, but in your case you will have to enumerate all, except id, which will be generated I suppose.
– ZorgoZ
Nov 25 '18 at 9:18
The table has 32 fields, so trying to avoid large queries. Can I increase the id number of that row, from the table I am copying from. I intend to delete this after the row is copied, so should not hurt anything. If so, all I need to do, is get the highest id from the table I am copying to, then add 1!
– Zast
Nov 25 '18 at 9:27
add a comment |
Please forgive if already answered. I have looked at lots of other questions and none are helping! Basically, I want to copy a single row from one table, to another table. The problem I have struck, is the the id of the row I want to copy, is already in use, in the table I want to copy to. This is what I have at present, it works, until it strikes this problem.
INSERT INTO venues SELECT * FROM submitted_venues WHERE id='9';
It generates the error: Duplicate entry '9' for key 'id'
Is there a way to change the id number on the fly, so that it create an unused new number for the receiving table, or do I have to write a query that names and gets all of the fields for each table. Some thing like the replace statement 0 cases would be good, where 0 creates a new record!!
The 9, is just a number that I used for this example, of course it changes.
EDIT:
I have created a work around for this problem.
//get highest id number of table to copy to
$sql = "SELECT id FROM venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$to_id = $row[ 0 ];
//increase id
$to_id ++;
//get highest id number of table to copy from
$sql = "SELECT id FROM submitted_venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$from_id = $row[ 0 ];
//increase id
$from_id ++;
//get highest unused id number
$temp_id = $to_id > $from_id ? $to_id : $from_id;
//update existing record to ensure that its id number will not conflict
$sql = "UPDATE submitted_venues SET id = '$temp_id' WHERE id='$current_venue_id'";
if( $result = mysqli_query( $mysqli, $sql ) ){
$current_venue_id = $temp_id;
//copy record to normal regular venues table
$sql = "INSERT INTO venues SELECT * FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not copy -> " . mysqli_error($mysqli) );
//delete record from temp db table
$sql = "DELETE FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not delete -> " . mysqli_error($mysqli) );
}
}
}
mysql copy row
asked Nov 25 '18 at 9:12
ZastZast
2401318
Please forgive if already answered. I have looked at lots of other questions and none are helping! Basically, I want to copy a single row from one table, to another table. The problem I have struck, is the the id of the row I want to copy, is already in use, in the table I want to copy to. This is what I have at present, it works, until it strikes this problem.
INSERT INTO venues SELECT * FROM submitted_venues WHERE id='9';
It generates the error: Duplicate entry '9' for key 'id'
Is there a way to change the id number on the fly, so that it create an unused new number for the receiving table, or do I have to write a query that names and gets all of the fields for each table. Some thing like the replace statement 0 cases would be good, where 0 creates a new record!!
The 9, is just a number that I used for this example, of course it changes.
EDIT:
I have created a work around for this problem.
//get highest id number of table to copy to
$sql = "SELECT id FROM venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$to_id = $row[ 0 ];
//increase id
$to_id ++;
//get highest id number of table to copy from
$sql = "SELECT id FROM submitted_venues ORDER BY id DESC LIMIT 1";
if( $result = mysqli_query( $mysqli, $sql ) ){
$row = mysqli_fetch_array($result);
$from_id = $row[ 0 ];
//increase id
$from_id ++;
//get highest unused id number
$temp_id = $to_id > $from_id ? $to_id : $from_id;
//update existing record to ensure that its id number will not conflict
$sql = "UPDATE submitted_venues SET id = '$temp_id' WHERE id='$current_venue_id'";
if( $result = mysqli_query( $mysqli, $sql ) ){
$current_venue_id = $temp_id;
//copy record to normal regular venues table
$sql = "INSERT INTO venues SELECT * FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not copy -> " . mysqli_error($mysqli) );
//delete record from temp db table
$sql = "DELETE FROM submitted_venues WHERE id='$current_venue_id'";
if( ! $result = mysqli_query( $mysqli, $sql ) )
die("Could not delete -> " . mysqli_error($mysqli) );
}
}
}
mysql copy row
mysql copy row
asked Nov 25 '18 at 9:12
ZastZast
2401318
asked Nov 25 '18 at 9:12
ZastZast
2401318
edited Nov 25 '18 at 10:22
Zast
asked Nov 25 '18 at 9:12
ZastZast
2401318
asked Nov 25 '18 at 9:12
ZastZast
2401318
asked Nov 25 '18 at 9:12
ZastZast
2401318
2401318
Take a look at ‘ON DUPLICATE KEY’ dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
– Adam Brinded
Nov 25 '18 at 9:15
There is no syntax that allows you to exclude one column from the asterix wilcard. You can add calclulated columns, but in your case you will have to enumerate all, except id, which will be generated I suppose.
– ZorgoZ
Nov 25 '18 at 9:18
The table has 32 fields, so trying to avoid large queries. Can I increase the id number of that row, from the table I am copying from. I intend to delete this after the row is copied, so should not hurt anything. If so, all I need to do, is get the highest id from the table I am copying to, then add 1!
– Zast
Nov 25 '18 at 9:27
add a comment |
Take a look at ‘ON DUPLICATE KEY’ dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
– Adam Brinded
Nov 25 '18 at 9:15
There is no syntax that allows you to exclude one column from the asterix wilcard. You can add calclulated columns, but in your case you will have to enumerate all, except id, which will be generated I suppose.
– ZorgoZ
Nov 25 '18 at 9:18
The table has 32 fields, so trying to avoid large queries. Can I increase the id number of that row, from the table I am copying from. I intend to delete this after the row is copied, so should not hurt anything. If so, all I need to do, is get the highest id from the table I am copying to, then add 1!
– Zast
Nov 25 '18 at 9:27
Take a look at ‘ON DUPLICATE KEY’ dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
– Adam Brinded
Nov 25 '18 at 9:15
Take a look at ‘ON DUPLICATE KEY’ dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
– Adam Brinded
Nov 25 '18 at 9:15
There is no syntax that allows you to exclude one column from the asterix wilcard. You can add calclulated columns, but in your case you will have to enumerate all, except id, which will be generated I suppose.
– ZorgoZ
Nov 25 '18 at 9:18
There is no syntax that allows you to exclude one column from the asterix wilcard. You can add calclulated columns, but in your case you will have to enumerate all, except id, which will be generated I suppose.
– ZorgoZ
Nov 25 '18 at 9:18
The table has 32 fields, so trying to avoid large queries. Can I increase the id number of that row, from the table I am copying from. I intend to delete this after the row is copied, so should not hurt anything. If so, all I need to do, is get the highest id from the table I am copying to, then add 1!
– Zast
Nov 25 '18 at 9:27
The table has 32 fields, so trying to avoid large queries. Can I increase the id number of that row, from the table I am copying from. I intend to delete this after the row is copied, so should not hurt anything. If so, all I need to do, is get the highest id from the table I am copying to, then add 1!
– Zast
Nov 25 '18 at 9:27
add a comment |
0
active
oldest
votes
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%2f53466096%2fhow-to-copy-a-row-from-one-mysql-table-to-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53466096%2fhow-to-copy-a-row-from-one-mysql-table-to-another%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
Take a look at ‘ON DUPLICATE KEY’ dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
– Adam Brinded
Nov 25 '18 at 9:15
There is no syntax that allows you to exclude one column from the asterix wilcard. You can add calclulated columns, but in your case you will have to enumerate all, except id, which will be generated I suppose.
– ZorgoZ
Nov 25 '18 at 9:18
The table has 32 fields, so trying to avoid large queries. Can I increase the id number of that row, from the table I am copying from. I intend to delete this after the row is copied, so should not hurt anything. If so, all I need to do, is get the highest id from the table I am copying to, then add 1!
– Zast
Nov 25 '18 at 9:27