Updating one table from another

You cannot use an update query to add new records to a database, or to delete entire records from a database.

To add new records to a database you use an append query, and to delete whole records from a database you use a delete query.

This article describes how to create and run an update query in an Access desktop database.

You use an update query when you have to update or change existing data in a set of records.

In case of an inner join (like here), the unmatched row(s) just won't be updated.

But if you indeed meant a column that exists in one table and doesn't exist in the other, then I believe that's one more new question to ask separately.

Adding a WHERE clause would limit the result set of the JOINed table as well.

@Roger Ray what version of My SQL and what was your query, as this DOES infact function as stated. Col2)) UPDATE CTE SET Col1 = _Col1, Col2 = _Col2 statement on its own first to sanity check the results but it does requires you to alias the columns as above if they are named the same in source and target tables.

UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 FROM Table INNER JOIN other_table ON = other_WHERE Table.col1 ! = other_table.col2 OR ( other_table.col1 IS NOT NULL AND Table.col1 IS NULL ) OR ( other_table.col2 IS NOT NULL AND Table.col2 IS NULL ) ; WITH CTE AS (SELECT T1. So all the answers involving the FROM clause returned a syntax error. UPDATE suppliers SET supplier_name = (SELECT FROM customers WHERE customers.customer_id = suppliers.supplier_id) WHERE EXISTS (SELECT FROM customers WHERE customers.customer_id = suppliers.supplier_id); UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 --select Table.col1, other_table.col, Table.col2,other_table.col2, * FROM Table INNER JOIN other_table ON = other_UPDATE from SELECT with INNER JOIN in SQL Database Since there are too many replies of this post, which are most heavily up-voted, I thought I would provide my suggestion here too.This works by using UPDATE to iterate over the INNER JOIN.As such the ON functions as your WHERE clause and the INNER JOIN skips records that are not found in the JOINed table.------------------------------------------------------------------------- | name | family | phone | email | gender | phone2 | address | birthdate | ------------------------------------------------------------------------- in table #1 columns address and phone2 is empty and columns gender and birthdate values is same as table #2.How can I read data from table #2 and update address and phone2 in table #1 with values from table #2 address and phone columns when gender and birthdate is the same in each row?