CODEWITHSUNDEEP
sqldatabasesqlite
rkthebest
rkthebest

Reputation: 13

merging columns from 2 database using sql

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

Upvotes: 1

Views: 63

Answers (2)

Kuru
Kuru

Reputation: 21

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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)

Upvotes: 0

Related Questions