I am trying to update only certain rows in a table, but the others get NULL'd out

Question

I am trying to run a simple Update query on a certain set of rows in my sqlite db. It works, but it NULLs out all the other rows. Here is the query:

update table1 set col5 =(select col5 from table2 where table2.id = table1.id)

I know this is super easy, but I can't figure out what is going on. Can't I only update certain rows, and leave the rest alone?

Alex Deem · Answer 1 · 2011-12-06T00:42:30.243

2

try this:

update table1 inner join table2 on table1.id = table2.id
set table1.col5 = table2.col5

Edit:

Sorry, didn't realise it was sqlite. sqlite doesn't support joins in the update clause. My best guess is you could do the following:

update table1 set col5 = (select col5 from table2 where table2.id = table1.id)
where id IN (SELECT id FROM table2)

edited Dec 06 '11 at 00:42

answered Dec 06 '11 at 00:01

Alex Deem

Has anyone actually tried or reasoned about this? Its semantics are: set a.x = b.x where a.x = b.x. It will result in an updated timestamp at best. – The Nail Dec 06 '11 at 00:08
So I tried the query, and my simple sqlite browser throws an error thanks to the inner keyword...I know I've done this type of thing before, I just can't remember how to do it. – Lizza Dec 06 '11 at 00:11
I realised it was doing nothing, I figured he was just using placeholders for the column names... – Alex Deem Dec 06 '11 at 00:21
I'm so sorry guys, I didn't have it down right. I updated it with the correct values. I can't get the join to work though...I really would like to get to the bottom of this. Thanks! – Lizza Dec 06 '11 at 00:28

score 1 · Accepted Answer · edited May 23 '17 at 10:08

1

You are using the same columns you are updating to relate the two tables. You should use a primary or secondary key to relate the two tables.

edited May 23 '17 at 10:08

Community

answered Dec 06 '11 at 00:06

The Nail

In your original question, that is. The link is still relevant though. – The Nail Dec 06 '11 at 00:35
@oofgwen add this to the query: `where exists (select * from table2 where table2.id = table1.id)` to avoid setting NULL values on non matching rows. – The Nail Dec 06 '11 at 00:37
That last part was it!! THANK YOU! – Lizza Dec 06 '11 at 00:56

2 Answers2