insert and update query with same click event

[fiazhnd]

I want to insert and update the record (With SQL Query) with the same click event on BtnSave. i am using access

[mangakissa]

Do you mean the same table insert/update

[fiazhnd]

Yes, i want to create a query which checks the database table if ID(PK) exit it updates otherwise it creates a new record.

[JD]

Yes, i want to create a query which checks the database table if ID(PK) exit it updates otherwise it creates a new record.

Like a PostgreSQL UPSERT command? Don't know if MS Access supports that but it can be creatively done with an IF statement.

[fiazhnd]

I am using Delphi currently for project anyway if anyone can give me an example will great help.

[shobits1]

you need to do it into 2 separate queries like this:

first call the insert query to, add new record if none found.

Code: MySQL  [Select][+][-]

1. INSERT INTO Table1 ( ID, Col1, Col2, .... )
2. SELECT TOP 1 ID_VAL, COL1_VAL, COL2_VAL, ....
3. FROM Table1
4. WHERE ID_VAL NOT IN (SELECT ID FROM Table1 WHERE ID = ID_VAL);
5.  

then call the update query to update the record if exist

Code: MySQL  [Select][+][-]

1. UPDATE Table1 SET ID = ID_NEW, Col1 = Col1_NEW, Col2 = Col2_NEW, ....
2. WHERE ID = ID_VAL;
3.  

I don't know if this is the best way but it works.

[ASerge]

See also https://stackoverflow.com/questions/6199417/upserting-in-ms-access/37014004#37014004 or https://www.experts-exchange.com/questions/28713136/Can-I-use-the-SQL-MERGE-statement-in-a-query-in-Access-2010.html