sqlite update a column with itself

I got a table like this

a  b  c
-- -- -- 
1  1  10
2  1  0
3  1  0
4  4  20
5  4  0
6  4  0

The b column 'points' to 'a', a bit like if a is the parent. c was computed. Now I need to propagate the parent c value to their children.

The result would be

a  b  c
-- -- -- 
1  1  10
2  1  10
3  1  10
4  4  20
5  4  20
6  4  20

I can't make an UPDATE/SELECT combo that works

So far I got a SELECT that procuce the c column I'd like to get

select t1.c  from t t1 join t t2 on t1.a=t2.b;
c
----------
10
10
10
20
20
20

But I dunno how to stuff that into c

Thanx in advance Cheers, phi

Answers


You have to look up the value with a correlated subquery:

UPDATE t
SET c = (SELECT c
         FROM t AS parent
         WHERE parent.a = t.b)
WHERE c = 0;

I finnally found a way to copy back my initial 'temp' SELECT JOIN to table 't'. Something like this

create temp table u as select t1.c  from t t1 join t t2 on t1.a=t2.b;
update t set c=(select * from u where rowid=t.rowid);

I'd like to know how the 2 solutions, yours with 1 query UPDATE correlated SELECT, and mine that is 2 queries and 1 correlated query each, compare perf wise. Mine seems more heavier, and less aesthetic, yet regarding perf I wonder.

On the Algo side, yours take care not to copy the parent data, only copy child data, mine copy parent on itself, but that's a nop, yet consuming some cycles :)

Cheers, Phi


Need Your Help

Which MSBuild tasks initiate a call to SGen?

visual-studio-2010 visual-studio msbuild msbuild-task sgen

I have inherited a complex MSBuild script which builds & deploys multiple solutions. When I run the script I see the following error in log:

Java - substring issues

java string arraylist substring

I'm going to show all of my code here so you guys get a gist of what I'm doing.