In SQLite, how can I update a column in TABLE A with the values from a column in TABLE B?

Need help with this as I m having no luck.

Table A

id   groupid   
 1     100   
 2     101   
 3     102  

Table B

groupid   newid  
 100        100  
 101开发者_运维知识库        100   
 102        100 

Update Table A so that Table A becomes

id   groupid   
 1     100   
 2     100   
 3     100  

which uses TableB to get the newid.

Thanks in advance

sqlite doesn't support joins in updates, but you could use a subquery. try something like this:

update a
set groupid = coalesce(
 (select newid from b where groupid = a.groupid limit 1),
 groupid
);