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?
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)
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.
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;
Source: Stackoverflow.com