How to Copy Data from One Table to Another (Where Both Have Other Fields Too That Are Not in Common)

How to copy data from one table to another (where both have other fields too that are not in common)?

INSERT INTO Destination SELECT * FROM Source;

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

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

Copy data from one column to other column (which is in a different table)

In SQL Server 2008 you can use a multi-table update as follows:

UPDATE tblindiantime 
SET tblindiantime.CountryName = contacts.BusinessCountry
FROM tblindiantime 
JOIN contacts
ON -- join condition here

You need a join condition to specify which row should be updated.

If the target table is currently empty then you should use an INSERT instead:

INSERT INTO tblindiantime (CountryName)
SELECT BusinessCountry FROM contacts

Trying to copy matching columns from one table to another table in SQLite?

For each row in the Images table, the database has to look up the corresponding row in the ImagesMSCV table. Each such lookup requires a full table scan, except when there is an index on that column:

CREATE INDEX xxx ON ImagesMSCV(ImageId);

Selecting data from one table based upon matching values in another

I don't have SQLite at hand now, so please forgive typos... But won't an inner join be the good solution for that ?

c.execute("""SELECT item1, item2, item3 
               FROM TableA JOIN TableB USING (item1,item2,item3)
               WHERE xyz IS NULL")

This join will match rows on identical "triplet" (item1,item2,item3).

---

If you only have the three column item1..3 in common to your two tables, you might even want to go to a natural join:

c.execute("""SELECT item1, item2, item3 
               FROM TableA NATURAL JOIN TableB
               WHERE xyz IS NULL")

This is an inner join implicitly made on the columns having the same name in both of the tables.
I'm not a big fan of this though, as it might become a time bomb ready to explode the day someone inadvertently add some columns to your tables...

Find records from one table which don't exist in another

There's several different ways of doing this, with varying efficiency, depending on how good your query optimiser is, and the relative size of your two tables:

This is the shortest statement, and may be quickest if your phone book is very short:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

alternatively (thanks to Alterlife)

SELECT *
FROM   Call
WHERE  NOT EXISTS
  (SELECT *
   FROM   Phone_book
   WHERE  Phone_book.phone_number = Call.phone_number)

or (thanks to WOPR)

SELECT * 
FROM   Call
LEFT OUTER JOIN Phone_Book
  ON (Call.phone_number = Phone_book.phone_number)
  WHERE Phone_book.phone_number IS NULL

(ignoring that, as others have said, it's normally best to select just the columns you want, not '*')

Filter data in one SQLite database table by ids from another table in the same database

I use a semi-join for this problem when working with dbplyr. A semi-join between two tables returns every record from the first table where there is at least one match with a record in the second table. (An anti-join is similar, returning where there are no matches in the second table.)

This would look like:

prepared_condition = condition %>%
  filter(group == "high")

output = data %>%
  semi_join(prepared_condition, by = "id")

Mysql: Select rows from a table that are not in another

If you have 300 columns as you mentioned in another comment, and you want to compare on all columns (assuming the columns are all the same name), you can use a NATURAL LEFT JOIN to implicitly join on all matching column names between the two tables so that you don't have to tediously type out all join conditions manually:

SELECT            a.*
FROM              tbl_1 a
NATURAL LEFT JOIN tbl_2 b
WHERE             b.FirstName IS NULL

Select from one table where not in another

Expanding on Sjoerd's anti-join, you can also use the easy to understand SELECT WHERE X NOT IN (SELECT) pattern.

SELECT pm.id FROM r2r.partmaster pm
WHERE pm.id NOT IN (SELECT pd.part_num FROM wpsapi4.product_details pd)

Note that you only need to use ` backticks on reserved words, names with spaces and such, not with normal column names.

On MySQL 5+ this kind of query runs pretty fast.

On MySQL 3/4 it's slow.

Make sure you have indexes on the fields in question
You need to have an index on pm.id, pd.part_num.

---

Related Topics