sql - 基于 SQLite 中的单行切换一系列行的位

Question

我有一个表，其中包含“标题”或“正常”非标题条目的行。这由 INTEGER 跟踪亲和柱IsHeader .

同样，如果该行是“事件”，我有一个列跟踪。

使用表“条目”和用于查找相关行的另一列“MCL_Row”，我可以使用切换“事件”的值

UPDATE Entries SET(Active) = 
    (SELECT (~(Active&1))&(Active|1) WHERE MCL_Row = <target>) 
    WHERE MCL_Row = <target>;

这可行，但如果我想根据标题打开或关闭整个组，我不能使用

UPDATE Entries SET(Active) = 
    (SELECT (~(Active&1))&(Active|1) WHERE S_Type = <typenum> AND IsHeader=1)
    WHERE S_Type = <typenum>;

因为在这里，SELECT 子查询返回我想要的一个值，但更新了多行。结果，第一行得到正确的结果，随后的行满足 WHERE S_Type = <typenum>子句更新为 NULL值(value)。

如何使用本子条款返回的值来设置 UPDATE 使用的多行的值(相同)陈述？

编辑:也许这个问题最初有点不清楚，所以在数据之前/之后添加一些示例。

前:

MCL_Row  S_Type  Active  IsHeader
1        1       1       1
2        1       1       0
3        1       0       0
4        2       1       1
5        2       1       0
6        2       1       0

通过 header 设置 S_Type=1 激活后:

MCL_Row  S_Type  Active  IsHeader
1        1       1       1
2        1       1       0
3        1      >1<      0
4        2       1       1
5        2       1       0
6        2       1       0

通过 header 设置 S_Type=1 无效后:

MCL_Row  S_Type  Active  IsHeader
1        1      >0<      1
2        1      >0<      0
3        1       0       0
4        2       1       1
5        2       1       0
6        2       1       0

Answer 1

第一次查询

UPDATE  Entries 
SET     Active = 1-Active 
WHERE   MCL_Row = <target>
;

第二次查询

UPDATE  Entries

SET     Active =   (select    1-h.Active 
                    from      Entries as h 
                    where     h.S_Type   = Entries.S_Type 
                          and h.IsHeader = 1
                    )

WHERE   S_Type = <typenum>

演示

create table Entries (MCL_Row int,S_Type int,IsHeader int,active int);

insert into Entries (MCL_Row,S_Type,IsHeader,active) values
    (1,123,1,1)
   ,(2,123,0,0)
   ,(3,123,0,0)
   ,(4,123,0,1)
;

select * from Entries;

+---------+--------+----------+--------+
| MCL_Row | S_Type | IsHeader | active |
+---------+--------+----------+--------+
| 1       | 123    | 1        | 1      |
+---------+--------+----------+--------+
| 2       | 123    | 0        | 0      |
+---------+--------+----------+--------+
| 3       | 123    | 0        | 0      |
+---------+--------+----------+--------+
| 4       | 123    | 0        | 1      |
+---------+--------+----------+--------+

UPDATE  Entries

SET     Active =   (select    1-h.Active 
                    from      Entries as h 
                    where     h.IsHeader = 1
                          and h.S_Type   = Entries.S_Type 
                    )
                    
WHERE   S_Type = 123 
;

select * from Entries;        
    
+---------+--------+----------+--------+
| MCL_Row | S_Type | IsHeader | active |
+---------+--------+----------+--------+
| 1       | 123    | 1        | 0      |
+---------+--------+----------+--------+
| 2       | 123    | 0        | 1      |
+---------+--------+----------+--------+
| 3       | 123    | 0        | 1      |
+---------+--------+----------+--------+
| 4       | 123    | 0        | 1      |
+---------+--------+----------+--------+

UPDATE  Entries

SET     Active =   (select    1-h.Active 
                    from      Entries as h 
                    where     h.IsHeader = 1
                          and h.S_Type   = Entries.S_Type 
                    )
                    
WHERE   S_Type = 123 
;
        

select * from Entries;        

+---------+--------+----------+--------+
| MCL_Row | S_Type | IsHeader | active |
+---------+--------+----------+--------+
| 1       | 123    | 1        | 1      |
+---------+--------+----------+--------+
| 2       | 123    | 0        | 0      |
+---------+--------+----------+--------+
| 3       | 123    | 0        | 0      |
+---------+--------+----------+--------+
| 4       | 123    | 0        | 0      |
+---------+--------+----------+--------+