Update multiple tables with one query
0
Record to update
select *
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
Update these records as follows:
set observation_value_text = '.',
observation_value_numeric = NULL,
om.status = 'D',
em.status = 'D',
I need help to update and set this values from the results of the query above. Can any help me. I have tried to update the table but it failed.
sql-server edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
asked Nov 28 '18 at 2:08
MekusMekus
62
add a comment |
0
Record to update
select *
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
Update these records as follows:
set observation_value_text = '.',
observation_value_numeric = NULL,
om.status = 'D',
em.status = 'D',
I need help to update and set this values from the results of the query above. Can any help me. I have tried to update the table but it failed.
sql-server edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
asked Nov 28 '18 at 2:08
MekusMekus
62
1
I recommend familiarising yourself with theupdatestatement docs.microsoft.com/en-us/sql/t-sql/queries/…
– Dale Burrell
Nov 28 '18 at 2:14
add a comment |
0
0
0
Record to update
select *
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
Update these records as follows:
set observation_value_text = '.',
observation_value_numeric = NULL,
om.status = 'D',
em.status = 'D',
I need help to update and set this values from the results of the query above. Can any help me. I have tried to update the table but it failed.
sql-server edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
asked Nov 28 '18 at 2:08
MekusMekus
62
Record to update
select *
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
Update these records as follows:
set observation_value_text = '.',
observation_value_numeric = NULL,
om.status = 'D',
em.status = 'D',
I need help to update and set this values from the results of the query above. Can any help me. I have tried to update the table but it failed.
sql-server sql-server edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
asked Nov 28 '18 at 2:08
MekusMekus
62
edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
asked Nov 28 '18 at 2:08
MekusMekus
62
edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
edited Nov 28 '18 at 8:56
Dávid Laczkó
429128
429128
asked Nov 28 '18 at 2:08
MekusMekus
62
asked Nov 28 '18 at 2:08
MekusMekus
62
asked Nov 28 '18 at 2:08
MekusMekus
62
62
1
I recommend familiarising yourself with theupdatestatement docs.microsoft.com/en-us/sql/t-sql/queries/…
– Dale Burrell
Nov 28 '18 at 2:14
add a comment |
1
I recommend familiarising yourself with theupdatestatement docs.microsoft.com/en-us/sql/t-sql/queries/…
– Dale Burrell
Nov 28 '18 at 2:14
1
1
I recommend familiarising yourself with the
update statement docs.microsoft.com/en-us/sql/t-sql/queries/…– Dale Burrell
Nov 28 '18 at 2:14
I recommend familiarising yourself with the
update statement docs.microsoft.com/en-us/sql/t-sql/queries/…– Dale Burrell
Nov 28 '18 at 2:14
add a comment |
2 Answers
2
active
oldest
votes
0
Try this:
update X set
observation_value_text = '.'
, observation_value_numeric = NULL
, om.status = 'D'
, em.status = 'D'
from
(
select
*
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
) X
Let me know if it works?
answered Nov 28 '18 at 6:55
BirelBirel
478213
Looking back at this, you might get an error stating that you cannot update more than one table. Please provide the column names in all the tables in your question?
– Birel
Nov 28 '18 at 6:57
Hello Birel, thanks for your help. The two columns to be updated are: Event_Measurable and Observation_Measurable
– Mekus
Nov 28 '18 at 9:13
@Mekus, thanks, but these two columns, are they both in the same Table or in separate tables? If they are separate, I will have to alter my answer...
– Birel
Nov 28 '18 at 10:20
separate tables
– Mekus
Nov 28 '18 at 14:33
Could you please specify which column is in which table? I will then alter my answer...
– Birel
Nov 29 '18 at 6:55
|
show 1 more comment
0
Your set list references multiple tables: that is invalid as only one table can be updated with one statement. You need to run 2 update statements so you either repeat the join in the second update or first put the result in a temp table and join that to the actual to-be-updated table. You might want to put the whole thing to a transaction to be able to roll back on error. See this reference.
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
Thanks, David, I will check out the reference provided
– Mekus
Nov 28 '18 at 9: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
});
}
});
draft saved
draft discarded
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%2f53511062%2fupdate-multiple-tables-with-one-query%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
0
Try this:
update X set
observation_value_text = '.'
, observation_value_numeric = NULL
, om.status = 'D'
, em.status = 'D'
from
(
select
*
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
) X
Let me know if it works?
answered Nov 28 '18 at 6:55
BirelBirel
478213
Looking back at this, you might get an error stating that you cannot update more than one table. Please provide the column names in all the tables in your question?
– Birel
Nov 28 '18 at 6:57
Hello Birel, thanks for your help. The two columns to be updated are: Event_Measurable and Observation_Measurable
– Mekus
Nov 28 '18 at 9:13
@Mekus, thanks, but these two columns, are they both in the same Table or in separate tables? If they are separate, I will have to alter my answer...
– Birel
Nov 28 '18 at 10:20
separate tables
– Mekus
Nov 28 '18 at 14:33
Could you please specify which column is in which table? I will then alter my answer...
– Birel
Nov 29 '18 at 6:55
|
show 1 more comment
0
Try this:
update X set
observation_value_text = '.'
, observation_value_numeric = NULL
, om.status = 'D'
, em.status = 'D'
from
(
select
*
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
) X
Let me know if it works?
answered Nov 28 '18 at 6:55
BirelBirel
478213
Looking back at this, you might get an error stating that you cannot update more than one table. Please provide the column names in all the tables in your question?
– Birel
Nov 28 '18 at 6:57
Hello Birel, thanks for your help. The two columns to be updated are: Event_Measurable and Observation_Measurable
– Mekus
Nov 28 '18 at 9:13
@Mekus, thanks, but these two columns, are they both in the same Table or in separate tables? If they are separate, I will have to alter my answer...
– Birel
Nov 28 '18 at 10:20
separate tables
– Mekus
Nov 28 '18 at 14:33
Could you please specify which column is in which table? I will then alter my answer...
– Birel
Nov 29 '18 at 6:55
|
show 1 more comment
0
0
0
Try this:
update X set
observation_value_text = '.'
, observation_value_numeric = NULL
, om.status = 'D'
, em.status = 'D'
from
(
select
*
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
) X
Let me know if it works?
answered Nov 28 '18 at 6:55
BirelBirel
478213
Try this:
update X set
observation_value_text = '.'
, observation_value_numeric = NULL
, om.status = 'D'
, em.status = 'D'
from
(
select
*
from Event_Measurable em
join Observation_Measurable om on em.Event_GUID = om.Event_GUID
where observation_guid in (8786975, 285886, 85976, 786976)
) X
Let me know if it works?
answered Nov 28 '18 at 6:55
BirelBirel
478213
answered Nov 28 '18 at 6:55
BirelBirel
478213
answered Nov 28 '18 at 6:55
BirelBirel
478213
answered Nov 28 '18 at 6:55
BirelBirel
478213
478213
Looking back at this, you might get an error stating that you cannot update more than one table. Please provide the column names in all the tables in your question?
– Birel
Nov 28 '18 at 6:57
Hello Birel, thanks for your help. The two columns to be updated are: Event_Measurable and Observation_Measurable
– Mekus
Nov 28 '18 at 9:13
@Mekus, thanks, but these two columns, are they both in the same Table or in separate tables? If they are separate, I will have to alter my answer...
– Birel
Nov 28 '18 at 10:20
separate tables
– Mekus
Nov 28 '18 at 14:33
Could you please specify which column is in which table? I will then alter my answer...
– Birel
Nov 29 '18 at 6:55
|
show 1 more comment
Looking back at this, you might get an error stating that you cannot update more than one table. Please provide the column names in all the tables in your question?
– Birel
Nov 28 '18 at 6:57
Hello Birel, thanks for your help. The two columns to be updated are: Event_Measurable and Observation_Measurable
– Mekus
Nov 28 '18 at 9:13
@Mekus, thanks, but these two columns, are they both in the same Table or in separate tables? If they are separate, I will have to alter my answer...
– Birel
Nov 28 '18 at 10:20
separate tables
– Mekus
Nov 28 '18 at 14:33
Could you please specify which column is in which table? I will then alter my answer...
– Birel
Nov 29 '18 at 6:55
Looking back at this, you might get an error stating that you cannot update more than one table. Please provide the column names in all the tables in your question?
– Birel
Nov 28 '18 at 6:57
Looking back at this, you might get an error stating that you cannot update more than one table. Please provide the column names in all the tables in your question?
– Birel
Nov 28 '18 at 6:57
Hello Birel, thanks for your help. The two columns to be updated are: Event_Measurable and Observation_Measurable
– Mekus
Nov 28 '18 at 9:13
Hello Birel, thanks for your help. The two columns to be updated are: Event_Measurable and Observation_Measurable
– Mekus
Nov 28 '18 at 9:13
@Mekus, thanks, but these two columns, are they both in the same Table or in separate tables? If they are separate, I will have to alter my answer...
– Birel
Nov 28 '18 at 10:20
@Mekus, thanks, but these two columns, are they both in the same Table or in separate tables? If they are separate, I will have to alter my answer...
– Birel
Nov 28 '18 at 10:20
separate tables
– Mekus
Nov 28 '18 at 14:33
separate tables
– Mekus
Nov 28 '18 at 14:33
Could you please specify which column is in which table? I will then alter my answer...
– Birel
Nov 29 '18 at 6:55
Could you please specify which column is in which table? I will then alter my answer...
– Birel
Nov 29 '18 at 6:55
|
show 1 more comment
0
Your set list references multiple tables: that is invalid as only one table can be updated with one statement. You need to run 2 update statements so you either repeat the join in the second update or first put the result in a temp table and join that to the actual to-be-updated table. You might want to put the whole thing to a transaction to be able to roll back on error. See this reference.
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
Thanks, David, I will check out the reference provided
– Mekus
Nov 28 '18 at 9:50
add a comment |
0
Your set list references multiple tables: that is invalid as only one table can be updated with one statement. You need to run 2 update statements so you either repeat the join in the second update or first put the result in a temp table and join that to the actual to-be-updated table. You might want to put the whole thing to a transaction to be able to roll back on error. See this reference.
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
Thanks, David, I will check out the reference provided
– Mekus
Nov 28 '18 at 9:50
add a comment |
0
0
0
Your set list references multiple tables: that is invalid as only one table can be updated with one statement. You need to run 2 update statements so you either repeat the join in the second update or first put the result in a temp table and join that to the actual to-be-updated table. You might want to put the whole thing to a transaction to be able to roll back on error. See this reference.
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
Your set list references multiple tables: that is invalid as only one table can be updated with one statement. You need to run 2 update statements so you either repeat the join in the second update or first put the result in a temp table and join that to the actual to-be-updated table. You might want to put the whole thing to a transaction to be able to roll back on error. See this reference.
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
edited Nov 28 '18 at 8:09
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
answered Nov 28 '18 at 7:42
Dávid LaczkóDávid Laczkó
429128
429128
Thanks, David, I will check out the reference provided
– Mekus
Nov 28 '18 at 9:50
add a comment |
Thanks, David, I will check out the reference provided
– Mekus
Nov 28 '18 at 9:50
Thanks, David, I will check out the reference provided
– Mekus
Nov 28 '18 at 9:50
Thanks, David, I will check out the reference provided
– Mekus
Nov 28 '18 at 9:50
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53511062%2fupdate-multiple-tables-with-one-query%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
I recommend familiarising yourself with the
updatestatement docs.microsoft.com/en-us/sql/t-sql/queries/…– Dale Burrell
Nov 28 '18 at 2:14