用两个表更新，我有一个一行的例子，但我必须更新168432行，使用sqlite

Question

我想更新sqlite表，下面是更新一行的示例：

update tpecroad set tnode = (SELECT  b.nodeid FROM "TPECRoad" as a
join tpecnode as b 
where pointn(a.geometry,numpoints(a.geometry)) = b.geometry and a.pk =1)
where pk=1

但是有168432行需要更新，有没有更快的方法来更新大量数据？

这就像改变了a.pk=1~168432和pk=1~1688432

非常感谢！！

Answer 1

update tpecroad as c
set tnode = ( SELECT  b.nodeid 
              FROM "TPECRoad" as a join tpecnode as b 
              where pointn(a.geometry,numpoints(a.geometry)) = b.geometry and a.pk = c.pk);

Answer 2

如果我正确理解这个问题，这个查询可能会有所帮助：

update tpecroad a set tnode = (
    select b.nodeid from tpecnode b
    where pointn(a.geometry,numpoints(a.geometry)) = b.geometry
)
where exists (
    select 1 from tpecnode b
    where pointn(a.geometry,numpoints(a.geometry)) = b.geometry
);

编辑：如果a.pk=b.pk必须验证才能执行更新，则查询将如下所示：

update tpecroad a set tnode = (
    select b.nodeid from tpecnode b
    where pointn(a.geometry,numpoints(a.geometry)) = b.geometry
        and a.pk = b.pk
)
where exists (
    select 1 from tpecnode b
    where pointn(a.geometry,numpoints(a.geometry)) = b.geometry
        and a.pk = b.pk
);

Answer 3

试试这个：

Update tpecroad a
set tnode = (SELECT b.nodeid 
FROM tpecnode as b 
where pointn(tpecroad.geometry,numpoints(tpecroad.geometry)) = b.geometry)