swapping to columns ended up with blank table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a table with 15 columns and 2 rows. I decided to swap the Month and Year columns so I run following query:
UPDATE test_schema.my_table_name
SET volume = month_value,
month_value=year_value,
year_value=volume
;
Volume column was empty so I used it as a temporary column. When I run the code all the table got empty!All 15 columns! would you please let me know why this happened?
The table was just a test table, is there any way to get the data back after running a wrong query?or the data is lost permanently?
sql postgresql sql-update
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
asked Nov 26 '18 at 15:43
SabaSaba
447
|
show 1 more comment
I have a table with 15 columns and 2 rows. I decided to swap the Month and Year columns so I run following query:
UPDATE test_schema.my_table_name
SET volume = month_value,
month_value=year_value,
year_value=volume
;
Volume column was empty so I used it as a temporary column. When I run the code all the table got empty!All 15 columns! would you please let me know why this happened?
The table was just a test table, is there any way to get the data back after running a wrong query?or the data is lost permanently?
sql postgresql sql-update
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
asked Nov 26 '18 at 15:43
SabaSaba
447
1
No temp column should be needed, simply doUPDATE tablename SET col1 = col2, col2 = col1.
– jarlh
Nov 26 '18 at 15:49
1
Do you want to swap values between rows or between columns?
– a_horse_with_no_name
Nov 26 '18 at 15:53
@a_horse_with_no_name I just want to swap values of month and year columns
– Saba
Nov 26 '18 at 15:56
@jarlh sorry it is not the right answer. By following your query, both columns will have column 2 value and column 1 will be lost
– Saba
Nov 26 '18 at 15:58
@saba: Postgres is not MySQL, jarlh's statement will work. No intermediate column necessary.
– a_horse_with_no_name
Nov 26 '18 at 16:01
|
show 1 more comment
I have a table with 15 columns and 2 rows. I decided to swap the Month and Year columns so I run following query:
UPDATE test_schema.my_table_name
SET volume = month_value,
month_value=year_value,
year_value=volume
;
Volume column was empty so I used it as a temporary column. When I run the code all the table got empty!All 15 columns! would you please let me know why this happened?
The table was just a test table, is there any way to get the data back after running a wrong query?or the data is lost permanently?
sql postgresql sql-update
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
asked Nov 26 '18 at 15:43
SabaSaba
447
I have a table with 15 columns and 2 rows. I decided to swap the Month and Year columns so I run following query:
UPDATE test_schema.my_table_name
SET volume = month_value,
month_value=year_value,
year_value=volume
;
Volume column was empty so I used it as a temporary column. When I run the code all the table got empty!All 15 columns! would you please let me know why this happened?
The table was just a test table, is there any way to get the data back after running a wrong query?or the data is lost permanently?
sql postgresql sql-update
sql postgresql sql-update
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
asked Nov 26 '18 at 15:43
SabaSaba
447
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
asked Nov 26 '18 at 15:43
SabaSaba
447
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
edited Nov 26 '18 at 15:51
a_horse_with_no_name
307k46468567
307k46468567
asked Nov 26 '18 at 15:43
SabaSaba
447
asked Nov 26 '18 at 15:43
SabaSaba
447
asked Nov 26 '18 at 15:43
SabaSaba
447
447
1
No temp column should be needed, simply doUPDATE tablename SET col1 = col2, col2 = col1.
– jarlh
Nov 26 '18 at 15:49
1
Do you want to swap values between rows or between columns?
– a_horse_with_no_name
Nov 26 '18 at 15:53
@a_horse_with_no_name I just want to swap values of month and year columns
– Saba
Nov 26 '18 at 15:56
@jarlh sorry it is not the right answer. By following your query, both columns will have column 2 value and column 1 will be lost
– Saba
Nov 26 '18 at 15:58
@saba: Postgres is not MySQL, jarlh's statement will work. No intermediate column necessary.
– a_horse_with_no_name
Nov 26 '18 at 16:01
|
show 1 more comment
1
No temp column should be needed, simply doUPDATE tablename SET col1 = col2, col2 = col1.
– jarlh
Nov 26 '18 at 15:49
1
Do you want to swap values between rows or between columns?
– a_horse_with_no_name
Nov 26 '18 at 15:53
@a_horse_with_no_name I just want to swap values of month and year columns
– Saba
Nov 26 '18 at 15:56
@jarlh sorry it is not the right answer. By following your query, both columns will have column 2 value and column 1 will be lost
– Saba
Nov 26 '18 at 15:58
@saba: Postgres is not MySQL, jarlh's statement will work. No intermediate column necessary.
– a_horse_with_no_name
Nov 26 '18 at 16:01
1
1
No temp column should be needed, simply do
UPDATE tablename SET col1 = col2, col2 = col1.– jarlh
Nov 26 '18 at 15:49
No temp column should be needed, simply do
UPDATE tablename SET col1 = col2, col2 = col1.– jarlh
Nov 26 '18 at 15:49
1
1
Do you want to swap values between rows or between columns?
– a_horse_with_no_name
Nov 26 '18 at 15:53
Do you want to swap values between rows or between columns?
– a_horse_with_no_name
Nov 26 '18 at 15:53
@a_horse_with_no_name I just want to swap values of month and year columns
– Saba
Nov 26 '18 at 15:56
@a_horse_with_no_name I just want to swap values of month and year columns
– Saba
Nov 26 '18 at 15:56
@jarlh sorry it is not the right answer. By following your query, both columns will have column 2 value and column 1 will be lost
– Saba
Nov 26 '18 at 15:58
@jarlh sorry it is not the right answer. By following your query, both columns will have column 2 value and column 1 will be lost
– Saba
Nov 26 '18 at 15:58
@saba: Postgres is not MySQL, jarlh's statement will work. No intermediate column necessary.
– a_horse_with_no_name
Nov 26 '18 at 16:01
@saba: Postgres is not MySQL, jarlh's statement will work. No intermediate column necessary.
– a_horse_with_no_name
Nov 26 '18 at 16:01
|
show 1 more comment
2 Answers
2
active
oldest
votes
No need for an temporary column, you can just swap the two columns:
UPDATE test_schema.my_table_name
SET year_value = month_value,
month_value = year_value;
This will work as the right side of an assignment is evaluate before the update is run.
Online example: https://rextester.com/TIYDE85991
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
add a comment |
It' strange that jarlh's proposal did not work, try 3 statements:
UPDATE test_schema.my_table_name
SET volume = month_value;
UPDATE test_schema.my_table_name
SET month_value=year_value;
UPDATE test_schema.my_table_name
SET year_value=volume;
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
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%2f53484594%2fswapping-to-columns-ended-up-with-blank-table%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
No need for an temporary column, you can just swap the two columns:
UPDATE test_schema.my_table_name
SET year_value = month_value,
month_value = year_value;
This will work as the right side of an assignment is evaluate before the update is run.
Online example: https://rextester.com/TIYDE85991
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
add a comment |
No need for an temporary column, you can just swap the two columns:
UPDATE test_schema.my_table_name
SET year_value = month_value,
month_value = year_value;
This will work as the right side of an assignment is evaluate before the update is run.
Online example: https://rextester.com/TIYDE85991
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
add a comment |
No need for an temporary column, you can just swap the two columns:
UPDATE test_schema.my_table_name
SET year_value = month_value,
month_value = year_value;
This will work as the right side of an assignment is evaluate before the update is run.
Online example: https://rextester.com/TIYDE85991
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
No need for an temporary column, you can just swap the two columns:
UPDATE test_schema.my_table_name
SET year_value = month_value,
month_value = year_value;
This will work as the right side of an assignment is evaluate before the update is run.
Online example: https://rextester.com/TIYDE85991
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
answered Nov 26 '18 at 16:03
a_horse_with_no_namea_horse_with_no_name
307k46468567
307k46468567
add a comment |
add a comment |
It' strange that jarlh's proposal did not work, try 3 statements:
UPDATE test_schema.my_table_name
SET volume = month_value;
UPDATE test_schema.my_table_name
SET month_value=year_value;
UPDATE test_schema.my_table_name
SET year_value=volume;
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
add a comment |
It' strange that jarlh's proposal did not work, try 3 statements:
UPDATE test_schema.my_table_name
SET volume = month_value;
UPDATE test_schema.my_table_name
SET month_value=year_value;
UPDATE test_schema.my_table_name
SET year_value=volume;
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
add a comment |
It' strange that jarlh's proposal did not work, try 3 statements:
UPDATE test_schema.my_table_name
SET volume = month_value;
UPDATE test_schema.my_table_name
SET month_value=year_value;
UPDATE test_schema.my_table_name
SET year_value=volume;
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
It' strange that jarlh's proposal did not work, try 3 statements:
UPDATE test_schema.my_table_name
SET volume = month_value;
UPDATE test_schema.my_table_name
SET month_value=year_value;
UPDATE test_schema.my_table_name
SET year_value=volume;
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
answered Nov 26 '18 at 16:01
forpasforpas
19.8k4830
19.8k4830
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%2f53484594%2fswapping-to-columns-ended-up-with-blank-table%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
No temp column should be needed, simply do
UPDATE tablename SET col1 = col2, col2 = col1.– jarlh
Nov 26 '18 at 15:49
1
Do you want to swap values between rows or between columns?
– a_horse_with_no_name
Nov 26 '18 at 15:53
@a_horse_with_no_name I just want to swap values of month and year columns
– Saba
Nov 26 '18 at 15:56
@jarlh sorry it is not the right answer. By following your query, both columns will have column 2 value and column 1 will be lost
– Saba
Nov 26 '18 at 15:58
@saba: Postgres is not MySQL, jarlh's statement will work. No intermediate column necessary.
– a_horse_with_no_name
Nov 26 '18 at 16:01