根据另一个表中的值更新一个表中的字段

本文介绍了根据另一个表中的值更新一个表中的字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在一个表中有一个状态字段，在其中将测试步骤标记为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 )