Update Query to update or insert 1

[BrenoAguiar]

what would be the SQL to run a query that updates a record in table with the data from a another table using the same primary key fields. Now, if the record (based on the primary key field) does not exist in the destination table, then INSERT the record!

Source tbl: "inventory"
destination tbl: "inventory_count"

primary key field: [unitID]
other fields: [field1] and [field2]


Thanks for the help

 

[PHV]

You have to play with 2 queries.
1) Append query:
INSERT INTO inventory_count (unitID,field1,field2)
SELECT unitID,field1,field2 FROM inventory
WHERE unitID Not In (SELECT unitID FROM inventory_count);

2) Update query:
UPDATE inventory_count C INNER JOIN inventory I ON C.unitID = I.unitID
SET C.field1 = I.field1, C.field2 = I.field2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrenoAguiar]

That worked great. I also have a form that has two unbound text boxes: [field1x] and [field2x] . And those have the control set with a formula to calculate a number based on what the user typed in the [field1] and [field2]. I've been transferring these values to my table "inventory_status" by using the following:

Dim subm2 As Recordset
Set subm2 = CurrentDb.OpenRecordset("inventory_status", dbOpenDynaset)

With subm2
.AddNew
![ComputerCode] = Me.[ComputerCode]
![field1x] = Me.[field1x]
![field2x] = Me.[field2x]
.Update
End With

subm2.Close


How can I UPDATE ONLY the "Inventory_status" table if the record with the respective [ComputerCode] field already exists? And, of course, add a new record if that [ComputerCode] does not exist yet in the Inventory_status table?

Thanks Again

 

[PHV]

With subm2
.FindFirst "ComputerCode='" & Me.[ComputerCode] & "'"
If .NoMatch Then
.AddNew
![ComputerCode] = Me.[ComputerCode]
Else
.Edit
End If
![field1x] = Me.[field1x]
![field2x] = Me.[field2x]
.Update
End With

If ComputerCode is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrenoAguiar]

Great! Thats perfect. Thanks.