Search code examples
sqldatabasesqlitesubquerysql-insert

How to subtract two column values from two tables and insert in 3rd table

I'm using SQlite

Table A: one column to store subtracted value(difference) Table B: one column with unix timestamp(OFF_time) Table C: one column with unix timestamp(ON_time)

HAVE to subtract timestamps of columns OFF_time and ON_time from tables B and C respectively and store in A.

This is what i tried:

 insert into A (difference) select B.OFF_time-C.ON_time from B,C;

[EDITED]

and got no error, but Table A is empty and not updated with the difference.

SAMPLE DATA:

TABLE C: ON_time

TABLE B: OFF_time

DESIRED OUPUT: TABLE A:

difference(column name in table A)

622

1024

608

398

458

Solution

  • You can use ROW_NUMBER() window function to join B and C:

    insert into A (difference) 
select t1.OFF_time - t2.ON_time 
from (select *, row_number() over (order by OFF_time) rn from B) t1
inner join (select *, row_number() over (order by ON_time) rn from C) t2
on t2.rn = t1.rn;