Update and Append Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables - Master & Import.
They both have the same fields and structure.

I would like to update the Master table with the information within the
Import table.

If Import.F6 matches any value within Master.F6 then I need Master.F2
updated with the value in Import.F2

However if Import.F6 does not exist within Master.F6 then I need a new
record appened to the Master table.

I hope this understandable.
Thanks,

Simon
 
Simon,

You need two separate queries, an Update and an Append one. The trick is to
use the right filter; the easiest way is to use a subquery in the criterion
on the F6 field.

So, the criterion in the Update query should be:

In (SELECT F6 FROM Master)

While in the Append query it should be:

Not In (SELECT F6 FROM Master)

The rest should be staright-forward to do in query design.

HTH,
Nikos
 
Nikos Yannacopoulos said:
Simon,

You need two separate queries, an Update and an Append one. The trick is
to
use the right filter; the easiest way is to use a subquery in the
criterion
on the F6 field.
Click to expand...

No you don't. One query will do both.

Tom Lake
 
Tom Lake said:
No you don't. One query will do both.

Tom Lake
Click to expand...

How would you do that then Tom? I have to confess I thought he would have
to use two aswell.
If it is possible with one query it would make some of my databases far
tidier.

Thanks in advance,

John.
 
You need two separate queries, an Update and an Append one. The trick
How would you do that then Tom? I have to confess I thought he would have
to use two aswell.
If it is possible with one query it would make some of my databases far
tidier.
Click to expand...

OK, lets say you have a Master and a Transaction table with Trans ID being
the PK in both. You want to add the day's transactions to the Master table
and modify any records that have changed throughout the day. You add the
Master table to an Update query, then add the Transaction table. Now drag a
line from the Trans ID in the Transaction table to the Trans ID in the
Master table. Make the join select all records from the Transaction table
so the arrowhead points to the Master table. This is called a right join
although the arrow will be pointing left if you added the tables to the
query in the order I specified. Now drag down all (or as many as you want
updated) the fields from the Master table to the grid. In the "Update To"
row add fields from the Transaction table. When you run this query, new
records will be added and existing records will be changed. It's that
simple. Here's my code:

UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];


Tom Lake
 
UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];
Click to expand...

Thanks Tom,

I'll give that a try.
 
John Ortt said:
UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];
Click to expand...

Thanks Tom,

I'll give that a try.
Click to expand...

Worked a treat.

Thankyou very much
 
John Ortt said:
John Ortt said:
UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans
ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];
Click to expand...

Thanks Tom,

I'll give that a try.
Click to expand...

Worked a treat.

Thankyou very much
Click to expand...

Glad I could help.

Tom L
 
Tom Lake said:
How would you do that then Tom? I have to confess I thought he would have
to use two aswell.
Click to expand...

UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];
Click to expand...

If it seems counterintuitive, that's because this UPDATE syntax is
unique to MS Jet. It bears no relation to how a standard SQL UPDATE
should be.

Jamie.

--
 
No you don't. One query will do both.
How would you do that then Tom? I have to confess I thought he would
have
to use two aswell.
Click to expand...

UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];
Click to expand...

If it seems counterintuitive, that's because this UPDATE syntax is
unique to MS Jet. It bears no relation to how a standard SQL UPDATE
should be.

Jamie.
Click to expand...

I know nothing about SQL. How should a standard SQL query to do a
simultaneous append and update read?

Tom L
 
As far as I Know a "standard" SQL statement would not do a simultaneous Append
and Update. It would do one and then the other. Now it could do the two
separate actions inside a transaction which could have the same result.


Tom said:
No you don't. One query will do both.

How would you do that then Tom? I have to confess I thought he would
have
to use two aswell.

UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];
Click to expand...

If it seems counterintuitive, that's because this UPDATE syntax is
unique to MS Jet. It bears no relation to how a standard SQL UPDATE
should be.

Jamie.
Click to expand...

I know nothing about SQL. How should a standard SQL query to do a
simultaneous append and update read?

Tom L
Click to expand...
 
John Spencer (MVP) said:
As far as I Know a "standard" SQL statement would not do a simultaneous Append
and Update. It would do one and then the other. Now it could do the two
separate actions inside a transaction which could have the same result.
Click to expand...

If you create an update query that sets the value of every field in TableA
to the value in joined TableB and use an outer join then records that exist
in TableB, but not in TableA will be appended to TableA while records found
in both tables will be updated in TableA.

I'm not sure if many other dbs support this, but it does work in Jet.
 
Yes, JET is the exception we were talking about. It is different than SQL of
most other SQL databases.
 
I have a similar update, however I want to limit the updated fields to one,
and want to make sure I've got my SQL/Jet understanding correct.

My tables have 3 keys: TransID, Month, Year (both tables set as the same
format) and when all 3 keys are the same, update the name & DOB.


UPDATE Master RIGHT JOIN [Transaction]
ON Master.[Trans ID] = Transaction.[Trans ID]
AND Master.[Month] = Transaction.[Month]
AND Master.[Year] = Transaction.[Year]
SET Master.[Trans ID] = [Transaction].[Trans ID], Master.Name =
[Transaction].[Name], Master.DOB = [Transaction].[DOB];

Is my new statement correct?

Thank you,
Tom Lake said:
How would you do that then Tom? I have to confess I thought he would have
to use two aswell.
If it is possible with one query it would make some of my databases far
tidier.
Click to expand...

OK, lets say you have a Master and a Transaction table with Trans ID being
the PK in both. You want to add the day's transactions to the Master table
and modify any records that have changed throughout the day. You add the
Master table to an Update query, then add the Transaction table. Now drag a
line from the Trans ID in the Transaction table to the Trans ID in the
Master table. Make the join select all records from the Transaction table
so the arrowhead points to the Master table. This is called a right join
although the arrow will be pointing left if you added the tables to the
query in the order I specified. Now drag down all (or as many as you want
updated) the fields from the Master table to the grid. In the "Update To"
row add fields from the Transaction table. When you run this query, new
records will be added and existing records will be changed. It's that
simple. Here's my code:

UPDATE Master RIGHT JOIN [Transaction] ON Master.[Trans ID] =
Transaction.[Trans ID] SET Master.[Trans ID] = [Transaction].[Trans ID],
Master.Name = [Transaction].[Name], Master.DOB = [Transaction].[DOB];


Tom Lake
Click to expand...
 
What do you want to do with Records in [Transaction] that don't have
matching Records in [Master]?

If you want to *add* them into [Master], use Right Join. If you don't, use
Inner Join.

Try( Right Join example):

UPDATE Master RIGHT JOIN [Transaction]
ON Master.[Trans ID] = Transaction.[Trans ID]
AND Master.[Month] = Transaction.[Month]
AND Master.[Year] = Transaction.[Year]
SET Master.Name = [Transaction].[Name],
Master.DOB = [Transaction].[DOB];

I am not sure whay you meant by "to limit the updated fields to one"? You
later stated that you wanted to update [Name] and [DOB]!
 
I meant that I had a similar situation where some records needed to be
updated in a master database from a transaction database.

You answered my question, tyvm.

Van T. Dinh said:
What do you want to do with Records in [Transaction] that don't have
matching Records in [Master]?

If you want to *add* them into [Master], use Right Join. If you don't, use
Inner Join.

Try( Right Join example):

UPDATE Master RIGHT JOIN [Transaction]
ON Master.[Trans ID] = Transaction.[Trans ID]
AND Master.[Month] = Transaction.[Month]
AND Master.[Year] = Transaction.[Year]
SET Master.Name = [Transaction].[Name],
Master.DOB = [Transaction].[DOB];

I am not sure whay you meant by "to limit the updated fields to one"? You
later stated that you wanted to update [Name] and [DOB]!

--
HTH
Van T. Dinh
MVP (Access)


Keith Meier said:
I have a similar update, however I want to limit the updated fields to one,
and want to make sure I've got my SQL/Jet understanding correct.

My tables have 3 keys: TransID, Month, Year (both tables set as the same
format) and when all 3 keys are the same, update the name & DOB.


UPDATE Master RIGHT JOIN [Transaction]
ON Master.[Trans ID] = Transaction.[Trans ID]
AND Master.[Month] = Transaction.[Month]
AND Master.[Year] = Transaction.[Year]
SET Master.[Trans ID] = [Transaction].[Trans ID], Master.Name =
[Transaction].[Name], Master.DOB = [Transaction].[DOB];

Is my new statement correct?

Thank you,
Click to expand...
Click to expand...
 
I have a very similar problem how ever I am using
two tables
table1 tracks companies and there company codes. PK is company code
table2 tracks the companies transactions. PK is autonumber
when I run

SELECT Data.Symbol, Data.[Company Name], Data.Last, Data.Volume, Data.[Rel
Str], Data.Date
FROM Data LEFT JOIN Symbols ON Data.[Company Name] = Symbols.[Company Name]
WHERE (((Symbols.[Company Name]) Is Null));

I get 143 records how ever when I try to update or append I don't get that
number.
I think it is because I have multiple company codes table2 so if AA isn't in
table1 and it is in table2 4 times then it is trying to pull over more info
than it needs.

So can someone help?
Thanks,
RogueIT
 
Back
Top