0

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?

1

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

1
  • Thank you so much for this answer. I have been struggling with this for past 2 days. – Pranjal Jun 14 '19 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.