如果所有值在另一个表中匹配，如何更新? [英] How to Update if all value matches in another table ?

问题描述

我有2个表BLL和BML.
如果表(BML)中的相关ID具有全部状态值(关闭)，我想更新表(BLL)的状态(关闭).

数据结构如下
_______________________________
表名称: BLL
========================
Batch_Id< tab>状态
--------------------------------
1个待处理
2待处理
3待处理(查询运行后应为关闭")
4待处理(查询运行后应为关闭")

表名称: BML
====================
Batch_Id状态
-----------------------
1个待处理
1关闭

2个待处理
2关闭

3关闭
3关闭

4关闭
4关闭
-----------------------


注意:此处BML表的每个ID可以有多个值
流的逻辑也是我们不能传递ID的值进行比较.因此，必须考虑所有表记录&相关数据必须更新.

如何进行查询?
是否需要循环?我们可以避免吗?
提前谢谢.

问候，
Yogesh

I have 2 Tables BLL & BML .
I want to update Table (BLL)''s status(Close) if related IDs in Table(BML) have All status Values (Close).

Data Structure as below
_______________________________
Table Name : BLL
==========================
Batch_Id <tab> Status
--------------------------------
1 Pending
2 Pending
3 Pending (Should be "Close" after query run)
4 Pending (Should be "Close" after query run)

Table Name : BML
=====================
Batch_Id Status
-----------------------
1 Pending
1 Close

2 Pending
2 Close

3 Close
3 Close

4 Close
4 Close
-----------------------


Note : Here BML table can have multiple Values for Each ID
Also logic of flow is that we can not pass value of ID to compare. so all records of tables must be considered & related data must be updated.

how to make Query ?
Is loop will be necessary ? Can we avoid it ?
Thanks in advance.

Regards,
Yogesh

推荐答案

我认为这可以做到:

I think this will do it:

UPDATE
  BLL
SET
  BLL.Status = 'Close'
FROM
  BLL
    INNER JOIN
  (SELECT BML.Batch_Id,
    FROM BML
    INNER JOIN BLL ON BML.Batch_Id = BLL.Batch_Id
    GROUP BY BML.Batch_Id
    HAVING Count(Distinct BML.Status) = 1 AND MIN(BML.Status) = 'Close') CloseList
  ON
    BLL.Batch_Id = CloseList.Batch_Id

因此，嵌套查询将创建一个有效关闭状态的列表，该列表将连接到目标表以限制要更新的行.

该查询创建从源表到目标表的联接，并按批次ID分组，仅选择状态为1且状态值为"Close"的行.

So the nested query creates a list of the valid close states which is joined to the target table to restrict the rows which are updated.

The query creates a join from the source table to target table and groups on batch id selecting just rows where there is one 1 distinct status and the value of the status is ''Close''.

SELECT BML.Batch_Id,
    FROM BML
    INNER JOIN BLL ON BML.Batch_Id = BLL.Batch_Id
    GROUP BY BML.Batch_Id
    HAVING Count(Distinct BML.Status) = 1 AND MIN(BML.Status) = 'Close'

由 Stephen Hewison 2 给出的解决方案1很好.

或者，当Status 表
中所有对应的Batch_Ids关闭Status 时，可以将以下query 用于BLL中的Update Status

The Solution 1 given by Stephen Hewison 2 is good.

Alternatively, the following query can be used to Update Status in BLL when the Status is close for all corresponding Batch_Ids in BML table

UPDATE
  BLL
SET
  BLL.Status = 'Close'
WHERE
  BLL.Batch_Id 
  IN 
    (
        --List all Batch_Id which have all status as Close
	SELECT BML.Batch_Id
	FROM BML
	GROUP BY BML.Batch_Id
	HAVING SUM(CASE WHEN bml.Status <> 'Close' THEN 1 ELSE 0 END)  = 0
    )

WHERE 子句中的子查询列出了所有Batch_Ids ，这些Status 字段在BML 表中具有所有Close 作为Close ，从而确保仅使用Close更新BLL 表中的Batch_Id 状态. .

The sub query in the WHERE clause list all Batch_Ids which have all Status field as Close in BML table which ensures that only those Batch_Id status in BLL table is updated with Close.

亲爱的朋友，

希望此示例查询对您有所帮助.

Dear Friend,

Hope this sample query will be helping you.

update BLL set status='CLOSE' from
--select * from
BLL a (nolock), BML b (nolock) where a.ID=b.ID

问候，
AP

Regards,
AP

这篇关于如果所有值在另一个表中匹配，如何更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！