Mixing
Update field in one table based on values in another table
I have a status field in a table in which test steps are marked as pass or fail and in another table I have to update test case(testID) as pass if all test step(testID) are pass, and fail test case if one of test is fail.
I have testID field common in both tables.
In the 1st table one column is testID and 5 steps are corresponding to that test ID and they can be pass or fail.
In the 2nd table I have one column in which I have to mark status as pass or fail based on overall 5 steps.
Table 1
Table 2
ms-access
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
asked Nov 16 '18 at 11:44
ravi
32
add a comment |
I have a status field in a table in which test steps are marked as pass or fail and in another table I have to update test case(testID) as pass if all test step(testID) are pass, and fail test case if one of test is fail.
I have testID field common in both tables.
In the 1st table one column is testID and 5 steps are corresponding to that test ID and they can be pass or fail.
In the 2nd table I have one column in which I have to mark status as pass or fail based on overall 5 steps.
Table 1
Table 2
ms-access
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
asked Nov 16 '18 at 11:44
ravi
32
add a comment |
I have a status field in a table in which test steps are marked as pass or fail and in another table I have to update test case(testID) as pass if all test step(testID) are pass, and fail test case if one of test is fail.
I have testID field common in both tables.
In the 1st table one column is testID and 5 steps are corresponding to that test ID and they can be pass or fail.
In the 2nd table I have one column in which I have to mark status as pass or fail based on overall 5 steps.
Table 1
Table 2
ms-access
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
asked Nov 16 '18 at 11:44
ravi
32
I have a status field in a table in which test steps are marked as pass or fail and in another table I have to update test case(testID) as pass if all test step(testID) are pass, and fail test case if one of test is fail.
I have testID field common in both tables.
In the 1st table one column is testID and 5 steps are corresponding to that test ID and they can be pass or fail.
In the 2nd table I have one column in which I have to mark status as pass or fail based on overall 5 steps.
Table 1
Table 2
ms-access
ms-access
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
asked Nov 16 '18 at 11:44
ravi
32
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
asked Nov 16 '18 at 11:44
ravi
32
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
edited Nov 19 '18 at 13:56
Lee Mac
3,48031339
3,48031339
asked Nov 16 '18 at 11:44
ravi
32
asked Nov 16 '18 at 11:44
ravi
32
asked Nov 16 '18 at 11:44
ravi
32
32
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The following queries perform the update in two steps: the 1st query updates those tests which exist and pass, and the 2nd query updates those tests which exist and fail.
Update 'passed' tests:
update table1 t1
set t1.teststatus = 'pass'
where t1.testID in
(
select t2.testID
from table2 t2
group by t2.testID
having min(t2.status) = max(t2.status) and min(t2.status) = 'pass'
)
Update 'failed' tests:
update table1 t1
set t1.teststatus = 'fail'
where t1.testID in
(
select t2.testID
from table2 t2
where t2.status = 'fail'
group by t2.testID
)
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.
– ravi
Nov 17 '18 at 15:50
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.for some steps pass or fail is not there means validation is not there for those steps so they have null values neither pass nor fail
– ravi
Nov 17 '18 at 16:01
@ravi Please edit your question to include some sample data.
– Lee Mac
Nov 17 '18 at 16:18
ID TestID stepid status 1 456 1 pass 2 456 2 pass 3 456 3 pass 4 457 1 fail 5 457 2 fail
– ravi
Nov 17 '18 at 16:53
1st table TestID testStatus 456 this need to update 457
– ravi
Nov 17 '18 at 16:57
|
show 5 more comments
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%2f53337213%2fupdate-field-in-one-table-based-on-values-in-another-table%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
The following queries perform the update in two steps: the 1st query updates those tests which exist and pass, and the 2nd query updates those tests which exist and fail.
Update 'passed' tests:
update table1 t1
set t1.teststatus = 'pass'
where t1.testID in
(
select t2.testID
from table2 t2
group by t2.testID
having min(t2.status) = max(t2.status) and min(t2.status) = 'pass'
)
Update 'failed' tests:
update table1 t1
set t1.teststatus = 'fail'
where t1.testID in
(
select t2.testID
from table2 t2
where t2.status = 'fail'
group by t2.testID
)
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.
– ravi
Nov 17 '18 at 15:50
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.for some steps pass or fail is not there means validation is not there for those steps so they have null values neither pass nor fail
– ravi
Nov 17 '18 at 16:01
@ravi Please edit your question to include some sample data.
– Lee Mac
Nov 17 '18 at 16:18
ID TestID stepid status 1 456 1 pass 2 456 2 pass 3 456 3 pass 4 457 1 fail 5 457 2 fail
– ravi
Nov 17 '18 at 16:53
1st table TestID testStatus 456 this need to update 457
– ravi
Nov 17 '18 at 16:57
|
show 5 more comments
The following queries perform the update in two steps: the 1st query updates those tests which exist and pass, and the 2nd query updates those tests which exist and fail.
Update 'passed' tests:
update table1 t1
set t1.teststatus = 'pass'
where t1.testID in
(
select t2.testID
from table2 t2
group by t2.testID
having min(t2.status) = max(t2.status) and min(t2.status) = 'pass'
)
Update 'failed' tests:
update table1 t1
set t1.teststatus = 'fail'
where t1.testID in
(
select t2.testID
from table2 t2
where t2.status = 'fail'
group by t2.testID
)
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.
– ravi
Nov 17 '18 at 15:50
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.for some steps pass or fail is not there means validation is not there for those steps so they have null values neither pass nor fail
– ravi
Nov 17 '18 at 16:01
@ravi Please edit your question to include some sample data.
– Lee Mac
Nov 17 '18 at 16:18
ID TestID stepid status 1 456 1 pass 2 456 2 pass 3 456 3 pass 4 457 1 fail 5 457 2 fail
– ravi
Nov 17 '18 at 16:53
1st table TestID testStatus 456 this need to update 457
– ravi
Nov 17 '18 at 16:57
|
show 5 more comments
The following queries perform the update in two steps: the 1st query updates those tests which exist and pass, and the 2nd query updates those tests which exist and fail.
Update 'passed' tests:
update table1 t1
set t1.teststatus = 'pass'
where t1.testID in
(
select t2.testID
from table2 t2
group by t2.testID
having min(t2.status) = max(t2.status) and min(t2.status) = 'pass'
)
Update 'failed' tests:
update table1 t1
set t1.teststatus = 'fail'
where t1.testID in
(
select t2.testID
from table2 t2
where t2.status = 'fail'
group by t2.testID
)
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
The following queries perform the update in two steps: the 1st query updates those tests which exist and pass, and the 2nd query updates those tests which exist and fail.
Update 'passed' tests:
update table1 t1
set t1.teststatus = 'pass'
where t1.testID in
(
select t2.testID
from table2 t2
group by t2.testID
having min(t2.status) = max(t2.status) and min(t2.status) = 'pass'
)
Update 'failed' tests:
update table1 t1
set t1.teststatus = 'fail'
where t1.testID in
(
select t2.testID
from table2 t2
where t2.status = 'fail'
group by t2.testID
)
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
edited Nov 19 '18 at 13:52
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
answered Nov 16 '18 at 22:51
Lee Mac
3,48031339
3,48031339
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.
– ravi
Nov 17 '18 at 15:50
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.for some steps pass or fail is not there means validation is not there for those steps so they have null values neither pass nor fail
– ravi
Nov 17 '18 at 16:01
@ravi Please edit your question to include some sample data.
– Lee Mac
Nov 17 '18 at 16:18
ID TestID stepid status 1 456 1 pass 2 456 2 pass 3 456 3 pass 4 457 1 fail 5 457 2 fail
– ravi
Nov 17 '18 at 16:53
1st table TestID testStatus 456 this need to update 457
– ravi
Nov 17 '18 at 16:57
|
show 5 more comments
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.
– ravi
Nov 17 '18 at 15:50
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.for some steps pass or fail is not there means validation is not there for those steps so they have null values neither pass nor fail
– ravi
Nov 17 '18 at 16:01
@ravi Please edit your question to include some sample data.
– Lee Mac
Nov 17 '18 at 16:18
ID TestID stepid status 1 456 1 pass 2 456 2 pass 3 456 3 pass 4 457 1 fail 5 457 2 fail
– ravi
Nov 17 '18 at 16:53
1st table TestID testStatus 456 this need to update 457
– ravi
Nov 17 '18 at 16:57
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.
– ravi
Nov 17 '18 at 15:50
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.
– ravi
Nov 17 '18 at 15:50
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.for some steps pass or fail is not there means validation is not there for those steps so they have null values neither pass nor fail
– ravi
Nov 17 '18 at 16:01
steps are not constant for all test cases. for some test case I have 5 steps and for some 7,for some 11.for some steps pass or fail is not there means validation is not there for those steps so they have null values neither pass nor fail
– ravi
Nov 17 '18 at 16:01
@ravi Please edit your question to include some sample data.
– Lee Mac
Nov 17 '18 at 16:18
@ravi Please edit your question to include some sample data.
– Lee Mac
Nov 17 '18 at 16:18
ID TestID stepid status 1 456 1 pass 2 456 2 pass 3 456 3 pass 4 457 1 fail 5 457 2 fail
– ravi
Nov 17 '18 at 16:53
ID TestID stepid status 1 456 1 pass 2 456 2 pass 3 456 3 pass 4 457 1 fail 5 457 2 fail
– ravi
Nov 17 '18 at 16:53
1st table TestID testStatus 456 this need to update 457
– ravi
Nov 17 '18 at 16:57
1st table TestID testStatus 456 this need to update 457
– ravi
Nov 17 '18 at 16:57
|
show 5 more comments
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%2f53337213%2fupdate-field-in-one-table-based-on-values-in-another-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
