merging columns from 2 database using sql

Question

I am working with two databases,primary and secondary. both contains same schema. now i cant to update column of primary database with the content of same column from secondary database provided the data in secondary database is not NULL.

ATTACH "secondary.db" as second;
UPDATE main.table set main.value = coalesce((SELECT value FROM second.table),main.value);

but the above code does not work and only the first value of the column from the secondary database is copied to all values in the column in primary database.

For Eg. primary database:

index, value
  1,    45
  2,    56
  3,    23

secondary database:

index, value
  1,   NULL
  2,   55
  3,   NULL

expected result database:

index, value
  1,    45
  2,    55
  3,    23

Any suggestions?

here you can see what is i am trying: http://sqlfiddle.com/#!5/845545/1

Please provide sample data and desired results. What columns link the rows in the two tables? — Gordon Linoff
– Gordon Linoff, Commented Aug 31, 2020 at 16:23

Kuru · Accepted Answer · 2020-09-02 14:32:58Z

1

I've not kept-up with SQLite for a few years, but I think you can now do it with an UPDATE FROM statement like this:

UPDATE 
   main
SET 
   main.value = Coalesce (main.value, second.value)
FROM
      main.table
   INNER JOIN
      second.table
   ON main.id = second.id

edited Sep 2, 2020 at 14:32

answered Aug 31, 2020 at 16:27

Kuru

216 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rkthebest Over a year ago

the priority is the secondary database, if the value exists in secondary database, it should overwrite value in primary database. How can i use JOIN or WHERE clause in my situation?

Kuru Over a year ago

Learning Resource: sqlite.org/lang_update.html

rkthebest Over a year ago

how can i access the second.value? i tried Coalesce (main.value, second.table.value) but it complains that "no such column"

Gordon Linoff Over a year ago

@Kuru . . . FYI. UPDATE FROM was introduced in SQLite 3.30, which was released in October 2019. If you haven't kept up with SQLite, then this syntax was never in SQLite. In addition, naming the table being updated is incorrect in SQLite.

Gordon Linoff · Accepted Answer · 2020-09-01 00:31:29Z

0

I think you need a correlated subquery:

update main.table t
    set main.value = coalesce((select t2.value from second.table t2 where t2.index = t.index),
                              main.value
                             );

Or, more efficiently. Filter the rows and don't use coalesce():

update main.table t
    set main.value = (select t2.value from second.table t2 where t2.index = t.index)
    where exists (select 1 from second.table t2 where t2.index = t.index and t2.value is not null)

edited Sep 1, 2020 at 0:31

answered Aug 31, 2020 at 17:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

rkthebest Over a year ago

both suggestion results in filling the primany database column with the 1st value of column from secondary database. :(

Gordon Linoff Over a year ago

@rkthebest . . . You are doing something wrong. The corresponding value in the second table is used, not the same value for every row.

rkthebest Over a year ago

sqlfiddle.com/#!5/845545/1 have a look here, this is what i am doing

Gordon Linoff Over a year ago

@rkthebest . . . If you write the query correctly -- as specified in the answer -- it will do what you want: sqlfiddle.com/#!5/845545/3.

rkthebest Over a year ago

Thank you very much for the help!! it completely solves my issue.

Collectives™ on Stack Overflow

merging columns from 2 database using sql

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related