Update with two table ,I had a example for one row,but I must update 168432 rows,used sqlite

Question

I want to update the sqlite table, the following is the example for updating one row:

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

but there are 168432 rows to be updated, is there any faster way to update the large amount of the data?

It's like changed a.pk=1~168432 and pk=1~168432

Thanks a lots!!

Are you saying you want to update records where pk is between 1 and 168432 i.e all the records? If so just remove both instances of pk = 1 — Christian Barron, Feb 16 '15 at 09:04
I meant That ,The stupid function is make 168432 querys ,and only changed the (a.pk =2 pk=2).. 3.4.5... — user3513254, Feb 16 '15 at 09:15
What code are you actually using to try update all the records? — Christian Barron, Feb 16 '15 at 09:27
update tpecroad set tnode = (SELECT b.nodeid FROM "TPECRoad" as a join tpecnode as b where pointn(a.geometry,numpoints(a.geometry)) = b.geometry ) //but all rows data are same — user3513254, Feb 16 '15 at 14:45

tonirush · Answer 1 · 2015-02-16T15:00:10.317

0

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);

edited Feb 16 '15 at 15:00

answered Feb 16 '15 at 09:47

tonirush

430
1
6
14

Insert 'as' in query about, could you check it again? – tonirush Feb 16 '15 at 15:02
It got a same error , http://stackoverflow.com/questions/773441/how-do-i-make-an-update-while-joining-tables-on-sqlite – user3513254 Feb 17 '15 at 01:35

Christian Barron · Answer 2 · 2015-02-23T09:20:19.133

0

Try this:

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

edited Feb 23 '15 at 09:20

answered Feb 16 '15 at 15:05

Christian Barron

2,695
1
14
22

It was running but the table's contents wasn't the answer.I thought the query in the brackets can't touched outside like tpecroad. – user3513254 Feb 22 '15 at 04:28
@user3513254 I've updated my answer, I hadn't realized this was sqlite – Christian Barron Feb 23 '15 at 09:20

antonio · Answer 3 · 2015-02-17T20:28:44.157

If I understand the question right, this query may help:

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
);

Edit: If a.pk = b.pk must verify to perform the update, the query will look as follows:

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
);

But the problem was .The sqlite can't used "update tpecroad a set tnode" and "update tpecroad as a set tnode" — user3513254, Feb 22 '15 at 04:32

Update with two table ,I had a example for one row,but I must update 168432 rows,used sqlite

3 Answers3