94

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. I can't make SQLite do this. In SQL Server this is a super simple task.

user4157124
  • 2,809
  • 13
  • 27
  • 42
Ian Vink
  • 66,960
  • 104
  • 341
  • 555
  • 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
  • Data Corruption Warning - If you are renaming tables at any point, there are certain patterns that risk corrupting table references in triggers, views, and foreign keys. As explicitly stated in the SQLite docs, Section 7 of https://www.sqlite.org/lang_altertable.html, the following steps may cause corruption: 1. Rename old tables 2. Create new table 3. Copy data 4. Drop old tables. The docs also explicitly lay out the recommended steps. Here is the summary from the docs: 1. Create new table 2. Copy data 3. Drop old table 4. Rename new into old – Tyler Aug 23 '23 at 17:24

7 Answers7

187
INSERT INTO Destination SELECT * FROM Source;

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

joschi
  • 12,746
  • 4
  • 44
  • 50
  • 2
    Not a valid answer because the question contain "though they both have other fields too that are not in common." End from SQLite manual: "If no column-list is specified, the number of columns in the result of the SELECT must be the same as the number of columns in the table". – Daniel Oct 05 '22 at 15:00
17

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
  • 954
  • 12
  • 21
9

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
  • 131
  • 1
  • 4
  • What is `country` and where is it coming from? Don't try to be clever with snippets like these please. Just show the basics. Like you did with the first statement. If you must get clever, please provide a description. – TheRealChx101 Oct 18 '22 at 17:23
4
insert into firstTable (column1, column2, ...) select column1, column2,...  from secondTable;
Simas Joneliunas
  • 2,890
  • 20
  • 28
  • 35
Jason Crook
  • 51
  • 1
  • 8
    Welcome to Stack Overflow! While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Yunnosch Jul 27 '22 at 19:47
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,728
  • 11
  • 46
  • 62
0

If you want to copy a single record from one table to a second, with a new column in the destination table,

INSERT INTO destination (updatedate,colum1,column2,column3) SELECT DATETIME('now','localtime'),colum1,column2,column3 FROM source WHERE id=1

In the destination insert, specify the columns you want to update from the source table, then in the source table SELECT statement, specify the same columns you want copied and in the above case the destination table requires a timestamp of the copy operation, in the source table SELECT statement, the column representing the new column will include a DATETIME('now','localtime');

SQLite handles multiple statements, so you can append a DELETE query to the end of the COPY operation,

INSERT INTO destination (updatedate,colum1,column2,column3) SELECT DATETIME('now','localtime'),colum1,column2,column3 FROM source WHERE id=1; DELETE FROM source WHERE id=1;
PHK Corporation
  • 31
  • 2
0

To copy column definitions from one table to another You cannot copy individual columns from one table to another existing table by using Transact-SQL statements. However, you can create a new table in the default filegroup and inserts the resulting rows from the query into it by using SELECT INTO. For more information, see INTO Clause (Transact-SQL). To copy data from one table to another In Object Explorer, connect to an instance of Database Engine.

On the Standard bar, click New Query.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.EmployeeSales  
( BusinessEntityID   varchar(11) NOT NULL,  
  SalesYTD money NOT NULL  
);  
GO  
INSERT INTO dbo.EmployeeSales  
    SELECT BusinessEntityID, SalesYTD   
    FROM Sales.SalesPerson;  
GO
borchvm
  • 3,533
  • 16
  • 44
  • 45
Joseph
  • 1
  • 1