根据另一个表中的值更新一个表中的字段 [英] Update field in one table based on values in another table
问题描述
我在一个表中有一个状态字段,在其中将测试步骤标记为pass
或fail
,并且在另一个表中,如果所有测试步骤(testID)均已完成,则我必须将测试用例(testID)更新为pass
pass
和fail
测试用例,如果其中一个测试是fail
.
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
.
我在两个表中都有testID
字段.
I have testID
field common in both tables.
在第一个表中,一列是testID
,对应于该测试ID的5个步骤,它们可以是pass
或fail
.
In the 1st table one column is testID
and 5 steps are corresponding to that test ID and they can be pass
or fail
.
在第二个表中,我有一列,必须基于总共5个步骤将状态标记为pass
或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.
推荐答案
以下查询分两步执行更新:第一个查询更新存在并通过的那些测试,第二个查询更新存在并失败的那些测试.
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 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 table1 t1
set t1.teststatus = 'fail'
where t1.testID in
(
select t2.testID
from table2 t2
where t2.status = 'fail'
group by t2.testID
)
这篇关于根据另一个表中的值更新一个表中的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!