从Sqlite的一个案例中更新

Question

我想更新一个表，必须测试每一行，并且根据另一个表的主键，文本字段必须更新为numéric值。这3个可能的文本值可以与3个数值中的一个相关联。我找到了这个请求，它在每一行都返回相同的第一个数值'2‘！

UPDATE HIV_adults_or_women_or_children_living_with_HIV SET indicateur_reduit=(
CASE 
WHEN ((select indicateur_in FROM HIV_adults_or_women_or_children_living_with_HIV) = "Number of adults aged 15 and over living with HIV") 
THEN (1)
WHEN ((SELECT indicateur_in FROM HIV_adults_or_women_or_children_living_with_HIV) = "Number of women aged 15 and over living with HIV" )
THEN (2)
WHEN ((select indicateur_in FROM HIV_adults_or_women_or_children_living_with_HIV) = "Number of children under 15 living with HIV")  
THEN (3)
END )

此外，我尝试了下一个无效的方法，因为它给出了"not this idResult“：

UPDATE HIV_adults_or_women_or_children_living_with_HIV SET indicateur_reduit=(
SELECT (SELECT ID FROM HIV_adults_or_women_or_children_living_with_HIV) as idResult,
CASE 
WHEN ((SELECT indicateur_in FROM HIV_adults_or_women_or_children_living_with_HIV WHERE id) = "Number of women aged 15 and over living with HIV" )
THEN (1)
WHEN ((select indicateur_in FROM HIV_adults_or_women_or_children_living_with_HIV WHERE id) = "Number of children under 15 living with HIV")  
THEN (2)
WHEN ((select indicateur_in FROM HIV_adults_or_women_or_children_living_with_HIV WHERE id) = "Number of adults aged 15 and over living with HIV") 
THEN (3)
END )
WHERE id=idResult

所以我可以通过Flex测试和更新每一行，首先我将使用select指令返回一行，然后我将更新它.这可能是一个有效的解决方案，但它会使很多麻烦，什么都没有，对许多工作。

Answer 1

您不需要子查询。想必，所有的信息都来自一个行：

UPDATE HIV_adults_or_women_or_children_living_with_HIV
    SET indicateur_reduit = (CASE WHEN indicateur_in = 'Number of adults aged 15 and over living with HIV'
                                  THEN 1
                                  WHEN indicateur_in = 'Number of women aged 15 and over living with HIV'
                                  THEN 2
                                  WHEN indicateur_in = 'Number of children under 15 living with HIV'
                                  THEN 3
                              END )