SQLite – Update Column Based on Matches with Another Table

sqlite

I have 2 tables say table1 containing says 3 columns col1, col2 and col3 and table2 which contains col1 and col2. I want to set col3 = 'some value' where table1.col1 = table2.col1 and table1.col2 = table2.col2. I found out that SQLite3 does not support join in updates like postgresql (with which I am familiar). How to achieve this?

Best Answer

You can try something like this, this is setting table1's col3 value to table2's col3 value where they match on col1 and col2. You can replace the col3 with your 'some value'.

create table table1 (
col1 int,
col2 int,
col3 int
);

create table table2 (
col1 int,  
col2 int,
col3 int
);

insert into table1 values (1,2,3);
insert into table1 values (4,5,6);

insert into table2 values (1,2,10);
insert into table2 values (4,5,20);

select * from table1;

update table1
set col3 = (
 select table2.col3
  from table2
 where table1.col1 = table2.col1
   and table1.col2 = table2.col2
)
where exists (
select table2.col3
  from table2
 where table1.col1 = table2.col1
   and table1.col2 = table2.col2
);

select * from table1;

DB Fiddle

Related Question