Trying to copy matching columns from one table to another table in SQLite?

Question

I have two tables called 'Images' and 'ImagesMSCV'. Both these tables contain the two columns 'MicrosoftId' and 'ImageId'. Right now the tables look like...

        Images                            ImagesMSCV
MicrosoftId | ImageId               MicrosoftId | ImageId
   null         234                     467        234 
   null         172                     512        172 
   null         376                     327        376 
   etc          etc                     etc        etc

So I'm trying to copy the 'MicrosoftId' column from 'ImagesMSCV' into the same column in 'Images'. To do this, I wrote the following SQLite statement...

UPDATE Images
  SET MicrosoftId =
    (SELECT MicrosoftId FROM ImagesMSCV
     WHERE ImagesMSCV.ImageId = Images.ImageId);

...however when I run this statement, my database just crashes. I don't know what's wrong with it?

EDIT: Apparently the database just stops responding because the UPDATE is taking so long. Any idea how to improve performance?

Answer 1

For each row in the Images table, the database has to look up the corresponding row in the ImagesMSCV table. Each such lookup requires a full table scan, except when there is an index on that column:

CREATE INDEX xxx ON ImagesMSCV(ImageId);