sqlite data from one db to another

Question

I have 2 sqlite databases, and I'm trying to insert data from one database to another. For example, "db-1.sqlite" has a table '1table' with 2 columns ('name', 'state'). Also, "db-2.sqlite" has a table '2table' with 2 columns ('name', 'url'). Both tables contain a list of 'name' values that are mostly common with each other but randomized, so the id of each row does not match.

I want to insert the values for the 'url' column into the db-1's table, but I want to make sure each url value goes to its corresponding 'name' value.

So far, I have done this:

> sqlite3 db-1.sqlite
sqlite> alter table 1table add column url;
sqlite> attach database 'db-2.sqlite' as db2;

Now, the part I'm not sure about:

sqlite> insert into 1table(url) select db2.2table.url from db2.2table where 1table.name==db2.2table.name

If you look at what I wrote above, you can tell what I'm trying to accomplish, but it is incorrect. If I can get any help on the matter, I'd be very grateful!!

score 5 · Accepted Answer · 2012-06-10 12:57:42Z

5

The equality comparison operator in SQL is =, not ==.

Also, I suspect that you should be updating 1table, rather then inserting in it.

Finally, your table names start with digits, so you need to escape them.

This SQL should work better:

update `1table` 
set url = (select db2.`2table`.url 
           from db2.`2table` 
           where `1table`.name = db2.`2table`.name);

edited Jun 10, 2012 at 12:57

answered Jun 10, 2012 at 12:41

user610650

Sign up to request clarification or add additional context in comments.

1 Comment

m10zart Over a year ago

ingenious! I am new to SQL. Thank you so much for your help!

Collectives™ on Stack Overflow

sqlite data from one db to another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related