sqlite join VS. select

Question

I'm having some trouble using sqlite3 to join records. I have two tables: tableA and tableB. tableA has the columns col1 and col2, while tableB has col3 and col4. I want to join col4 to tableA, based on the information in col2 and col3. So I added another column, col 5 to tableA and tried:

UPDATE tableA
SET col5 = 
(SELECT col4 from tableB
WHERE tableA.col2 = tableB.col3);

My tableA is about 300,000 rows, and tableB is more than a million rows, and this takes really long to run.
I understand that there is a way to do the same using a JOIN clause. So my questions are:
1. Will it matter? will it run faster if I use JOIN?
2. How do you do that using a join?
3. Are there other ways to improve performance?
Thanks a lot!

Answer 1

SQLite does not support joins in UPDATE statements (only in subqueries, but this would not make sense here).

Even if joins were supported, they would be implemented the same way (and have the same performance) as your query.

To speed this query up, the lookup of tableB rows by their col3 value needs to become faster. For that, create an index on the col3 column.