how to perform a correlated query operation in sqlite

Question

I am trying to update an sqlite table with values taken from another table but conditional on matching value in a key field but cannot figure out how to do it.

In postgres I can use "correlated query" as below

drop table if exists zzzz_tab_1;
drop table if exists zzzz_tab_2;
drop table if exists zzzz_tab_3;

create table zzzz_tab_1 (nr int , x_names varchar(20));
create table zzzz_tab_2 (nr int , x_age int);
create table zzzz_tab_3 (nr int , x_names varchar(20), x_age int);

INSERT INTO zzzz_tab_1 (nr, x_names) VALUES (1, 'AB');
INSERT INTO zzzz_tab_1 (nr, x_names) VALUES (2, 'BA');
INSERT INTO zzzz_tab_1 (nr, x_names) VALUES (3, 'CD');

INSERT INTO zzzz_tab_2 (nr, x_age) VALUES (1, 10);
INSERT INTO zzzz_tab_2 (nr, x_age) VALUES (3, 20);

-- add values 

-- add nr from zzzz_tab_1
insert into zzzz_tab_3 (nr) select nr from zzzz_tab_1;
--adding names from zzzz_tab_1
update zzzz_tab_3 
set 
  x_names = t1.x_names 
  from (select nr, x_names from zzzz_tab_1) as t1 
  where zzzz_tab_3.nr = t1.nr;
--adding age from zzzz_tab_2
update zzzz_tab_3 
set 
  x_age = t1.x_age 
  from (select nr, x_age from zzzz_tab_2) as t1 
  where zzzz_tab_3.nr = t1.nr;

select * from zzzz_tab_3;

But this does not seem to work in sqlite. I tried the following code based on the reply here, but it does also not work.

with tx1
as
(select nr, x_names from zzzz_tab_1)
replace into 
zzzz_tab_3
select 
zzzz_tab_3.nr, zzzz_tab_3.x_names
from zzzz_tab_3
inner join tx1 on tx1.nr = zzzz_tab_3.nr

Is this operation possible at all in sqlite ?

-- Clarification --

Basically I have two tables zzzz_tab_1 and zzzz_tab_3

zzzz_tab_1

nr  x_names
1   AB
2   BA
3   CD

zzzz_tab_3

nr  x_names
1    null
2    null       
3   null

I want to add data from zzzz_tab_1 to zzzz_tab_3 based on the value of the field the result (zzzz_tab_3) should be

zzzz_tab_3

nr  x_names
1   AB  
2   BA  
3   CD

P.S: One can create a new table with join but my tables are quite big (30 Mio Records)

Describe what the problem is that you're trying to solve, and what the result should be. — CL., Nov 09 '16 at 09:49

score 0 · Answer 1 · answered Jan 14 '17 at 15:13

In case someone else is interested, a colleague proposed this approach (and it works).

 update zzzz_tab_3
 set
 x_names = (select x_names from zzzz_tab_1 where zzzz_tab_3.nr =   zzzz_tab_1.nr);



update zzzz_tab_3
set
x_age = (select x_age from zzzz_tab_2  where zzzz_tab_3.nr = 
zzzz_tab_2.nr);

-- verify 
select * from zzzz_tab_3;

how to perform a correlated query operation in sqlite

1 Answers1