Update and append records in one table based on shared primary key

[nebreklaw]

Hello,
I am trying to update records on one table from another table. Both tables
have the same primary key data and I need to update or append the records in
the first table from records the second table. This should be
straightfoward, but I cannot figure it out.

 

[Graham Mandeno]

You can do this with two queries - one to update records that already have a
matching primary key, and a second to append records whose PK does not
already exist in the destination table.

The first query uses an inner join to update only matching records:

Update T1 inner join T2 on T1.PK1=T2.PK2
Set T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2.
.... etc

The second query uses a "NOT IN" subquery:

Insert into T1 (PK1, Field1, Field2, ... )
Select PK2, Field1, Field2, ... from T2
where PK2 not in (Select PK1 from T1)

 

[nebreklaw]

Thanks, but I'm afraid that is way over my head...is this some sort of SQL
query? I don't even know how to begin.

You can do this with two queries - one to update records that already have a
matching primary key, and a second to append records whose PK does not
already exist in the destination table.

The first query uses an inner join to update only matching records:

Update T1 inner join T2 on T1.PK1=T2.PK2
Set T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2.
... etc

The second query uses a "NOT IN" subquery:

Insert into T1 (PK1, Field1, Field2, ... )
Select PK2, Field1, Field2, ... from T2
where PK2 not in (Select PK1 from T1)


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,
I am trying to update records on one table from another table. Both
tables
have the same primary key data and I need to update or append the records
in
the first table from records the second table. This should be
straightfoward, but I cannot figure it out.

 

[Graham Mandeno]

ANY query is a SQL query. You can create the query using the query design
grid, or you enter the SQL directly into the SQL view window, but both
methods result in a SQL query.

Personally, I would enter queries like this into the SQL window, because
there is a lot of repetition that can be copied and pasted or, even better,
enter it into Word or Notepad and then copy/paste it to the SQL window.

If you want to use the grid, for the first one (the update), add both tables
to the design area and drag the primary key from the first table onto the
second PK to make a join. Then click "Query" on the menu and change it to
an update query.

Then you can double-click each of the fields in the destination table that
needs to be updated, which will add them to the grid.

Then, in the "Update to" cell for each of those fields, type:
[source table name].[source field name]

(Of course you must substitute the name of the table and field)

The other query is done in a similar way, except that it is an append query.
You will add only the source table to the query design, and specify the
destination table as the "Append to" table.

Double-click each of the source fields that will be appended, and in the
"Append To" cell, select the respective destination field for each. One of
these will be the PK field.

Finally, in the "Criteria" cell for the PK field, enter this:
not in (Select [name of PK in Dest table] from [name of dest table])

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks, but I'm afraid that is way over my head...is this some sort of SQL
query? I don't even know how to begin.

You can do this with two queries - one to update records that already
have a
matching primary key, and a second to append records whose PK does not
already exist in the destination table.

The first query uses an inner join to update only matching records:

Update T1 inner join T2 on T1.PK1=T2.PK2
Set T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2.
... etc

The second query uses a "NOT IN" subquery:

Insert into T1 (PK1, Field1, Field2, ... )
Select PK2, Field1, Field2, ... from T2
where PK2 not in (Select PK1 from T1)


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,
I am trying to update records on one table from another table. Both
tables
have the same primary key data and I need to update or append the
records
in
the first table from records the second table. This should be
straightfoward, but I cannot figure it out.

 

[nebreklaw]

Graham,
Thanks for the greater detail...I figured out the SQL window and created the
query after I posted my first reply...it took some editing before I got past
the various syntax errors. With your new suggestions, I'll make another run
at it when I go back to work.
Thanks again,
Ben

ANY query is a SQL query. You can create the query using the query design
grid, or you enter the SQL directly into the SQL view window, but both
methods result in a SQL query.

Personally, I would enter queries like this into the SQL window, because
there is a lot of repetition that can be copied and pasted or, even better,
enter it into Word or Notepad and then copy/paste it to the SQL window.

If you want to use the grid, for the first one (the update), add both tables
to the design area and drag the primary key from the first table onto the
second PK to make a join. Then click "Query" on the menu and change it to
an update query.

Then you can double-click each of the fields in the destination table that
needs to be updated, which will add them to the grid.

Then, in the "Update to" cell for each of those fields, type:
[source table name].[source field name]

(Of course you must substitute the name of the table and field)

The other query is done in a similar way, except that it is an append query.
You will add only the source table to the query design, and specify the
destination table as the "Append to" table.

Double-click each of the source fields that will be appended, and in the
"Append To" cell, select the respective destination field for each. One of
these will be the PK field.

Finally, in the "Criteria" cell for the PK field, enter this:
not in (Select [name of PK in Dest table] from [name of dest table])

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks, but I'm afraid that is way over my head...is this some sort of SQL
query? I don't even know how to begin.

You can do this with two queries - one to update records that already
have a
matching primary key, and a second to append records whose PK does not
already exist in the destination table.

The first query uses an inner join to update only matching records:

Update T1 inner join T2 on T1.PK1=T2.PK2
Set T1.Field1 = T2.Field1,
T1.Field2 = T2.Field2.
... etc

The second query uses a "NOT IN" subquery:

Insert into T1 (PK1, Field1, Field2, ... )
Select PK2, Field1, Field2, ... from T2
where PK2 not in (Select PK1 from T1)


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,
I am trying to update records on one table from another table. Both
tables
have the same primary key data and I need to update or append the
records
in
the first table from records the second table. This should be
straightfoward, but I cannot figure it out.