Update Several Records with separate table data

[mba.bcis]

I have two tables. One has all fields complete. The other has some
complete records, but most only have one field completed. I want to
input the complete records to the incomplete table, merging any
records that would be duplicated.

 

[Guest]

In Access (am unsure whether A2007 supports this), you can do this as a
single step, don't know about the current versions of SQL Server and Oracle.
Some people call this an UPSERT query, because you are doing both an update
and an insert at the same time. It looks something like:

UPDATE TableB AS b
RIGHT JOIN TableA AS a
ON b.ID = a.ID
SET b.Field1 = [a].[Field1], b.Field2 = [a].[Field2], b.Field3 = [a].[Field3];

This assumes that TableA is the more complete of the two tables. It takes
all of the records from table A, and either updates, or inserts Field1,
Field2, and Field3 into TableB. If a record with the same ID value already
exists in TableB then it will update the values of those fields. If no
record exists in TableB that matches the ID value in TableA, then a record
will be inserted.

In other systems, and for clarity, you might want to consider writing this
as two queries (an Update and an Insert).

HTH
Dale

 

[Guest]

I forgot to mention this in my first post.

Why are you duplicating data in your database? The goal of a well designed
database is to only store the same piece of information once.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

In Access (am unsure whether A2007 supports this), you can do this as a
single step, don't know about the current versions of SQL Server and Oracle.
Some people call this an UPSERT query, because you are doing both an update
and an insert at the same time. It looks something like:

UPDATE TableB AS b
RIGHT JOIN TableA AS a
ON b.ID = a.ID
SET b.Field1 = [a].[Field1], b.Field2 = [a].[Field2], b.Field3 = [a].[Field3];

This assumes that TableA is the more complete of the two tables. It takes
all of the records from table A, and either updates, or inserts Field1,
Field2, and Field3 into TableB. If a record with the same ID value already
exists in TableB then it will update the values of those fields. If no
record exists in TableB that matches the ID value in TableA, then a record
will be inserted.

In other systems, and for clarity, you might want to consider writing this
as two queries (an Update and an Insert).

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I have two tables. One has all fields complete. The other has some
complete records, but most only have one field completed. I want to
input the complete records to the incomplete table, merging any
records that would be duplicated.