Update and insert at the same query

[thelordoftherings]

Hello,

I would like to create this in access in one query like it is possible in SQL Server:
Make an update to a certain record where id = "111"
If 0 rows were updated than make an insert of new record

 

[dhookom]

You can do this in Access if you use a LEFT JOIN on primary key fields between the two tables in an update query. Update all the fields of your target table with fields from your source table. If a record doesn't exist in your target table, it will be added.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[thelordoftherings]

Can u please show me code example?

 

[dhookom]

I had a table based on another thread
tblOldTable
===============
[Name] text
[Occurance] numeric
[OtherField] text
where the combination of Name and Occurance was the primary key. I copied the table with the data into a new table (tblNewTable). Both tables had the same data and same primary key.

I then added some records to tblNewTable and created a query:

UPDATE tblNewTable LEFT JOIN tblOldTable ON (tblNewTable.Name = tblOldTable.Name) AND (tblNewTable.Occurance = tblOldTable.Occurance) SET tblOldTable.Name = [tblNewTable].[Name], tblOldTable.Occurance = [tblNewTable].[Occurance], tblOldTable.OtherField = [tblNewTable].[OtherField];


This query updated the OtherField values in tblOldTable as well as adding records from tblNewTable that were not previously included in tblOldTable.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.