sqlite copy data from one table to another

Question

SQLITE  I have 2 tables  Source  and  Destination  that have the same fields  ID and COUNTRY  though they both have other fields too that are not in common   I need to copy the Source Country value to the Destination Country where the join is on ID  For the life of me I can t make Sqlite do this   In SQL Server etc this is a super simple task   Ideas

User · Answer

If you have data already present in both the tables and you want to update a table  column values based on some condition then use this  UPDATE Table1 set Name  select t2 Name from Table2 t2 where t2 id Table1 id

User · Answer

If you re copying data like that  that probably means your datamodel isn t fully normalized  right  Is it possible to make one list of countries and do a JOIN more   Instead of a JOIN you could also use virtual tables so you don t have to change the queries in  your system

User · Answer

I ve been wrestling with this  and I know there are other options  but I ve come to the conclusion the safest pattern is   create table destination old as select   from destination   drop table destination   create table destination as select d    s country from destination old d left join source s on d id s id    It s safe because you have a copy of destination before you altered it  I suspect that update statements with joins weren t included in SQLite because they re powerful but a bit risky   Using the pattern above you end up with two country fields  You can avoid that by explicitly stating all of the columns you want to retrieve from destination old and perhaps using coalesce to retrieve the values from destination old if the country field in source is null  So for example   create table destination as select d field1  d field2     coalesce s country d country  country from destination old d left join source s on d id s id

User · Answer

INSERT INTO Destination SELECT   FROM Source    See SQL As Understood By SQLite  INSERT for a formal definition

[sql] sqlite copy data from one table to another

Examples related to sql

Examples related to sqlite