bulk update in SQLite

Question

I have 2 tables with identical structure I want to update one table using data from the other, matching on primary key. SQLite has a with (CTE) statement but the following doesn't work (sqlite3 v. 3.29.0):

sqlite> select * from main;
1|A
2|B
4|D
5|E
6|F
sqlite> select * from temp;
1|aa
2|bb
3|cc
4|dd
5|ee
sqlite> with mapping as (select main.ID, temp.Desc from main join temp on temp.ID=main.ID) update main set Desc=mapping.Desc where main.ID=mapping.ID;
Error: no such column: mapping.Desc

I've tried using "select main.ID as ID, temp.Desc as Desc", but get the same error message.

Answer 1

To update your main table from your cte, use a subquery, since sqlite doesn't support update from

with mapping as 
(select main.ID, temp.Desc 
 from main 
 join temp on temp.ID=main.ID) 
update main set Desc=
    (select Desc from mapping where ID = main.ID limit 1);

see dbfiddle