Updating table1 with table2

[Ellen]

I have a table for which I'd like to add new information
from a second table. There are identical fields in both
tables. Both tables have an ID# field, which is the
primary key.

I'd like to do two things:

1. Add unique records from table2 to table1.

2. Where there are common ID#s in both tables, I'd like
table1 to be updated with whatever table2 contains.

Can someone help me?

Thanks in advance.

Ellen

 

[Immanuel Sibero]

Hi Ellen,

Is there a good reason why the two tables? If they're identical, why not
have just one table?

1. Add unique records from table2 to table1.

- Index table1 based on the unique identifier (in your case it's probably
ID#) with 'No Duplicates' option.
- Use append query to insert records into table1 from table2.

2. Where there are common ID#s in both tables, I'd like
table1 to be updated with whatever table2 contains.

- Use an update query on table1. The Where clause should be "Where
[table1].[ID#] = [table2].[ID#]"


But I'm still interested in the reason why you have two tables with
identical structure.

HTH,
Immanuel Sibero

 

Ellen

Two queries are needed:

1) to append new records from Table1 into Table2

INSERT INTO Table1
SELECT T2.*
FROM Table2 AS T2 LEFT JOIN Table1 AS T1 ON T2.ID=T1.ID
WHERE T1.ID Is Null;

2) to update existing records in Table 1

UPDATE Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.ID=T2.ID
SET
T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2,
etc.

This however updates all regardless of whether there were any
differences or not. You can refine it to just alter changed records by
identifying those with changes in this query:

SELECT ID FROM
(
SELECT * FROM Table1
UNION
SELECT * FROM Table2
)
GROUP BY ID
HAVING Count(*)=2

which returns the ID of all modified records. If we call this query
'q_Changes' then the Update query can become:

UPDATE Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.ID=T2.ID
SET
T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2,
etc
WHERE T1.ID IN ( SELECT ID FROM q_Changes );

You can also employ q_Changes in logging which records have changed, and
archiving records prior to updating them.

 

[Guest]

Hello,
Thank you for your scripts. I got the first one to work.
I think I'll need more expertise to understand how to use
the second one, however. Nevertheless, I believe the
first one suits my purposes. If my newer table is table1
and my older table table2, it'll work. I'll get all the
records unique to both and where there are duplicate ID#s,
I'll get the information from the newer table.

I'll give it a try once I get back into the office on
Monday.

Many thanks!