CODEWITHSUNDEEP
sqlsqlite
elnigno
elnigno

Reputation: 1821

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

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".

Upvotes: 1

Views: 1136

Answers (1)

CL.
CL.

Reputation: 180310

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

Upvotes: 6

Related Questions