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

Question

PK | PARENT | COMMENT
---+--------+-----------
 1 |        | I AM ONE
 2 |        | I AM TWO
 3 |      1 | I AM ONE
 4 |      1 |
 5 |      2 | I AM TWO
 6 |      2 | I AM THREE

I want to update the COMMENT field of the rows where PARENT is not null, so that the COMMENT is the same as the parent. This is the expected result:

PK | PARENT | COMMENT
---+--------+---------
 1 |        | I AM ONE
 2 |        | I AM TWO
 3 |      1 | I AM ONE
 4 |      1 | I AM ONE
 5 |      2 | I AM TWO
 6 |      2 | I AM TWO

I've tried this query:

UPDATE MYTABLE
SET COMMENT =
(
select table2.COMMENT
from MYTABLE as table1
join MYTABLE as table2
on (table1.PARENT = table2.PK)
where table1.PARENT is not null
)
where PK in 
(
select table1.PK
from MYTABLE as table1
left outer join MYTABLE as table2
on (table1.PARENT = table2.PK)
where table1.PARENT is not null
)

but this results in all the COMMENT fields of rows with non-null PARENT to be set to "I AM ONE".

CL. · Accepted Answer · 2014-06-23 11:53:22Z

6

To update the rows where Parent is not NULL, use a statement like this:

UPDATE MyTable
SET ...
WHERE Parent IS NOT NULL

To look up the parent's comment, simply use a correlated subquery (which needs to refer to the table in the UPDATE statement, otherwise it wouldn't know which row is the current row being updated):

UPDATE MyTable
SET Comment = (SELECT Comment
               FROM MyTable AS MyParent
               WHERE MyParent.PK = MyTable.Parent)
WHERE Parent IS NOT NULL

answered Jun 23, 2014 at 11:53

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

elnigno Over a year ago

Thanks, this seems to do what I asked.

elnigno Over a year ago

I know, I'm just waiting some time to see if anyone else suggests another answer I like more.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related