Say I have two tables.

说我有两张桌子。

Table A

id
A_status
parent_id_B

Table B

id
B_status

So for each id in B can have many records in A.

因此,对于B中的每个id,可以在A中有许多记录。

Now my question is, I need to set B_status to 1 when all child entries in Table A with same parent_id_B has A_status =1, else set B_status = 2

现在我的问题是,当表A中具有相同parent_id_B的所有子条目具有A_status = 1时,我需要将B_status设置为1,否则设置B_status = 2

Ex:

Table A:
id    A_status    parent_id_B
1     1           1
2     1           1
3     1           2
4     1           3
5     1           3

Table B:
id    B_status
1     0
2     0
3     0

Expected result:
Table B:
id    B_status
1     1
2     1
3     1

Now consider another scenario

现在考虑另一种情况

Table A:
id    A_status    parent_id_B
1     1           1
2     1           1
3     2           2
4     2           3
5     1           3

Table B:
id    B_status
1     0
2     0
3     0

Expected result:
Table B:
id    B_status
1     1
2     2
3     2

I need this to work only on sqlite. Thanks

我需要这个只适用于sqlite。谢谢

1 个解决方案

#1

I believe this can be done like so:

我相信这可以这样做:

UPDATE TableB
SET B_Status = 
(SELECT MAX(A_Status) FROM TableA WHERE TableA.Parent_ID_B = TableB.ID);

SqlFiddle with your second case here

在这里你的第二个案例SqlFiddle

In a more general case (without relying on direct mapping of A's status, you can also use a CASE ... WHEN in the mapping:

在更一般的情况下(不依赖于A的状态的直接映射,您还可以在映射中使用CASE ... WHEN:

UPDATE TableB
SET B_Status = 
  CASE WHEN (SELECT MAX(A_Status) 
       FROM TableA 
       WHERE TableA.Parent_ID_B = TableB.ID) = 1
  THEN 1
  ELSE 2
END;

Edit (in the case where there are more than the original number of states):

编辑(如果有超过原始状态数的情况):

I believe you'll need to determine 2 facts about each row, e.g.

我相信你需要确定每行的2个事实,例如

Whether there is are any rows in table A with a status other than 1 for each B

表A中是否有任何行,每个B的状态不是1

And there must at least be one row for the same B

同一个B必须至少有一行

Or, whether the count of rows of A in state 1 = the count of all rows in A for the B.

或者,状态1中A的行数是否为B中A的所有行的计数。

Here's the first option:

这是第一个选择:

UPDATE TableB
SET B_Status = 
  CASE WHEN 
    EXISTS
     (SELECT 1 
      FROM TableA 
      WHERE TableA.Parent_ID_B = TableB.ID 
      AND TableA.A_Status <> 1)
    OR NOT EXISTS(SELECT 1 
      FROM TableA 
      WHERE TableA.Parent_ID_B = TableB.ID)
  THEN 2
  ELSE 1
END;

Updated Fiddle

#1