如何在SQLite中使用LEFT JOIN选择候选行进行更新 [英] How to UPDATE in SQLite using a LEFT JOIN to select candidate rows

问题描述

我有一个包含列的表assoc

local_id, remote_id, cachedData

我可以成功运行如下所示的SQLITE查询

I can successfully run an SQLITE query that looks like

SELECT a1.local_id, a1.remote_id FROM assoc a1 LEFT JOIN ....

以便我识别assoc表中符合我条件的某些行.

so that I identify certain rows of the assoc table that meet my criteria.

我想做的是在这些行中将cachedData设置为null.

What I would like to do is to set cachedData to null in those rows.

我该怎么做? Sqlite不支持带联接的UPDATE；它不支持UPDATE.您可以发出子查询，但我不知道如何正确获取语法；对我来说似乎不是直觉.

How can I do this? Sqlite doesn't support UPDATE with joins; you can issue subqueries but I can't figure out how to get the syntax correct; it seems nonintuitive to me.

推荐答案

 UPDATE assoc SET cachedData = NULL
    WHERE EXISTS (SELECT * FROM otherTable 
        WHERE otherTable.Col1 = assoc.Col1 AND otherTable.Col2 = assoc.Col1)

请注意，这并不是特别有效.

Be aware that this is not especially performant.

这篇关于如何在SQLite中使用LEFT JOIN选择候选行进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！