I try that solution but don't work. The reason is that in the subquery if
a field don't prefix with an alias (or a table name) the parser supouses
that was of de first table [[[--- Sorry about my poor Englis ---]]]
I try (and not works):
CREATE TABLE nodes(
id integer primary key,
father_id integer,
depth integer);
INSERT INTO nodes (id,father_id,depth) VALUES (9,null,1);
INSERT INTO nodes (id,father_id,depth) VALUES (99,9,null);
update nodes set depth=
(select f.depth+1
from nodes as f
where f.id = father_id)
where depth is null;
SELECT * FROM nodes;
Te output is:
9||1
99|9|
Must be:
9||1
99|9|2
I want to have something than do the same that the next sentences but
without de creating of a view:
CREATE TABLE nodes(
id integer primary key,
father_id integer,
depth integer);
INSERT INTO nodes (id,father_id,depth) VALUES (9,null,1);
INSERT INTO nodes (id,father_id,depth) VALUES (99,9,null);
CREATE VIEW nodes_father AS SELECT * FROM nodes;
update nodes set depth=
(select nodes_father.depth+1
from nodes_father
where nodes_father.id = nodes.father_id)
where depth is null;
SELECT * FROM nodes;
Te output is correct:
9||1
99|9|2
Thanks
> "Emilio Platzer" <[EMAIL PROTECTED]> wrote
> in message
> news:[EMAIL PROTECTED]
>> I want to UPDATE a field of one table with data of rows of the same
>> table.
>>
>> For example to calculate the depth of a node in a tree
>>
>> update nodes as s set s.depth=
>> (select f.depth+1
>> from nodes as f
>> where f.id=s.father_id)
>> where s.depth is null;
>
> You can't give an alias to the table you are updating, but you can use
> the same table (with an alias) in subselect. Just make it
>
> update nodes set s.depth=
> (select f.depth+1
> from nodes as f
> where f.id = father_id)
> where depth is null;
>
> Igor Tandetnik
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
