88

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?

Ian Vink
  • 63,888
  • 100
  • 326
  • 544
  • 1
    UPDATE Destination SET country= (select Country from source where id = Destination.id) WHERE EXISTS (select 1 from source where id = Destination.id); – Ian Vink Nov 27 '10 at 12:04
  • Would it be something a little like this? http://stackoverflow.com/questions/2717590/sqlite-upsert-on-duplicate-key-update – Tom Nov 27 '10 at 11:20

4 Answers4

177
INSERT INTO Destination SELECT * FROM Source;

See SQL As Understood By SQLite: INSERT for a formal definition.

joschi
  • 12,168
  • 4
  • 44
  • 49
  • 2
    that won;t work as I need to join on the ID field and only update one field (Country). – Ian Vink Nov 27 '10 at 11:17
  • 5
    You can easily modify the expression in the `SELECT` query to match your actual table schema. – joschi Nov 27 '10 at 12:17
  • 16
    Note for noobs (like me). If using multiple values, be sure to parenthesize your `SELECT` query e.g. `INSERT INTO users2(name, name2) VALUES ((SELECT name FROM users),(SELECT name2 FROM users));` – Sagar Pandya Jan 27 '19 at 14:23
  • 3
    @iceツ This just inserts one record in the table, though I have multiple records in my users table – Amardeep Kumar Agrawal Oct 22 '20 at 09:51
  • 1
    @iceツ Yeah, not sure why that comment got 10+ upvotes, as it's completely wrong. Here's how you do it: INSERT INTO new_table_name(col1, col2) SELECT col1, col2 FROM old_table_name; – Eternal21 Jan 26 '21 at 18:28
  • @Eternal21 I can't really remember too much about SQL or this to be honest, but I doubt I would have posted it unless it had worked for my use case. I guess the upvotes mean it must helped a few folk? – Sagar Pandya Jan 26 '21 at 22:21
16

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)
W00di
  • 934
  • 12
  • 21
  • Doing this will wipe data from table1 if table2 has fewer rows than table1. – zelusp Dec 22 '16 at 20:41
  • Nothing else worked but this one. in my case. thanks. SQLite -> UPDATE content set contentp=(select title.Title from title where title.PageNo = content.PageNo) – MindRoasterMir Mar 28 '21 at 15:00
6

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;
Max
  • 101
  • 1
  • 4
1

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.

Niels Bom
  • 8,090
  • 8
  • 44
  • 56
  • We are updating from one table to another is a data clean up exercise only. I just need to update the country fields (spelling changes) – Ian Vink Nov 27 '10 at 11:36