sql - Update multiple rows with values from another table and where clause in SQLite -

sql - Update multiple rows with values from another table and where clause in SQLite -

pk | parent | comment ---+--------+----------- 1 | | 1 2 | | 2 3 | 1 | 1 4 | 1 | 5 | 2 | 2 6 | 2 | 3

i want update comment field of rows parent not null, comment same parent. expected result:

pk | parent | comment ---+--------+--------- 1 | | 1 2 | | 2 3 | 1 | 1 4 | 1 | 1 5 | 2 | 2 6 | 2 | 2

i've tried query:

update mytable set comment = ( select table2.comment mytable table1 bring together mytable table2 on (table1.parent = table2.pk) table1.parent not null ) pk in ( select table1.pk mytable table1 left outer bring together mytable table2 on (table1.parent = table2.pk) table1.parent not null )

but results in comment fields of rows non-null parent set "i one".

to update rows parent not null, utilize statement this:

update mytable set ... parent not null

to parent's comment, utilize correlated subquery (which needs refer table in update statement, otherwise wouldn't know row current row beingness updated):

update mytable set comment = (select comment mytable myparent myparent.pk = mytable.parent) parent not null

sql sqlite