Solved Update a Record If It Exists and Insert If It Doesn't Exist (1 Viewer)

Pac-Man

Active member
Local time
Today, 14:36
Joined
Apr 14, 2020
Messages
408
Hello,

I'm trying to use the following SQL to update a record in a table:
SQL:
SQL = "UPDATE " & TblName & " SET " & TblName & ".SValue = '" & SValue & "' WHERE (((" & TblName & ".SKeyword)= '" & SKeyword & "'));"
'    Debug.Print SQL
    CurrentDb.Execute SQL

Where SQL, TblName, SValue and SKeyword are strong variables dimmed already.

I want to edit it so that it inserts a new record if it doesn't exist. Can it be done by modifying the SQL or do I have to use some if statement.

Best Regards,
Abdullah
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:36
Joined
Jan 20, 2009
Messages
12,849
Use an UPDATE query with a LEFT OUTER JOIN from the source to the destination table, joined on the key field you are comparing.

It is sometimes called an UPSERT query. Although this word is meaningless in SQL it will give results in Google.
 

isladogs

MVP / VIP
Local time
Today, 09:36
Joined
Jan 14, 2017
Messages
18,186
Normally you would use an unmatched append query to insert the new records and an update query to modify the existing records.
However in certain circumstances you can combine these into one query commonly known as an UPSERT or UPEND query. For more info, see UpEnd Query - Mendip Data Systems
EDIT Ah Greg got there a bit quicker
 

Pac-Man

Active member
Local time
Today, 14:36
Joined
Apr 14, 2020
Messages
408
Thanks @Galaxiom and @isladogs for reply. I've seen the demo in your website before posting here. But I don't have a second table. I just use this code to update one record at a time by inputting the value in code.

PS. I'm using this code save some settings in a table. Mostly it only require updating the value but sometime i have to insert new record.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:36
Joined
Sep 12, 2006
Messages
15,614
It wouldn't really matter if you did it in 2 steps.

1. A dlookup to see if it's there
2. if it does, then an update query OR
if it doesn't than an insert query.
 

Pac-Man

Active member
Local time
Today, 14:36
Joined
Apr 14, 2020
Messages
408
It wouldn't really matter if you did it in 2 steps.

1. A dlookup to see if it's there
2. if it does, then an update query OR
if it doesn't than an insert query.
Thanks a lot. That will work for me.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:36
Joined
May 7, 2009
Messages
19,175
dlookup will return Null if it does not find and the Field datatype if it does.
to be consistent, just use DCount() it always return number (0 if the criteria is not met, and "count" if it does).
 

Pac-Man

Active member
Local time
Today, 14:36
Joined
Apr 14, 2020
Messages
408
dlookup will return Null if it does not find and the Field datatype if it does.
to be consistent, just use DCount() it always return number (0 if the criteria is not met, and "count" if it does).
Thanks a lot @arnelgp.
 

Users who are viewing this thread

Top Bottom