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
3
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
- And there must at least be one row for the same B
- Or, whether the count of rows of A in state 1 = the count of all rows in A for the B.
表A中是否有任何行,每个B的状态不是1
同一个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;
#1
3
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
- And there must at least be one row for the same B
- Or, whether the count of rows of A in state 1 = the count of all rows in A for the B.
表A中是否有任何行,每个B的状态不是1
同一个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;