Question about join/update/insert sql query

Question

I am working on making a new sqlite database. Obviously for a number of reasons, structure and organization is important.

I have an existing database that is everything that I need, except for one column. So I duplicated the original db, and would like to just swap out that one column with new data from a different column in a different table in the db?

Can I do that? Which query is going to let me do that?

The join query just joins temporarily, right? If I need it to physically make the change in the db structure, do I use update?

I don't have tremendous amounts of experience with SQL queries, so any help here would be appreciated very much!

Answer 1

Without knowing your table structure, this will be the way to go:

UPDATE new_table
SET new_column = ( SELECT old_column
                   FROM old_table
                   WHERE old_table.id = new_table.id )

It updates new_column in your new_table with the value of old_table.old_column, where the id column matches.

You can replace the WHERE clause in the sub-select by whatever you have to find correlating records.

Answer 2

Yes you can create a query and insert it into your new structure. I'm assuming there is some kind of link between the 2 tables that you need to get the data from?

INSERT INTO TableName (field1, field2, field3) 
(SELECT field1, field2, Field3
FROM SomeTable 
INNER JOIN OtherTable on SomeTable.KeyField = OtherTable.FKField)