* * *

Author Topic: insert and update query with same click event  (Read 825 times)

fiazhnd

  • New member
  • *
  • Posts: 27
insert and update query with same click event
« on: August 10, 2017, 04:57:20 pm »
I want to insert and update the record (With SQL Query) with the same click event on BtnSave. i am using access
« Last Edit: August 10, 2017, 05:11:04 pm by fiazhnd »

mangakissa

  • Hero Member
  • *****
  • Posts: 748
Re: insert and update query with same click event
« Reply #1 on: August 11, 2017, 08:26:14 am »
Do you mean the same table insert/update
Lazarus 1.64 (32b) / FPC 3.0
Windows 10

fiazhnd

  • New member
  • *
  • Posts: 27
Re: insert and update query with same click event
« Reply #2 on: August 12, 2017, 01:05:51 pm »
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

  • Hero Member
  • *****
  • Posts: 1608
Re: insert and update query with same click event
« Reply #3 on: August 12, 2017, 01:33:03 pm »
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.
Windows (10, 7) - Lazarus 1.8/FPC 3.0.4, NewPascal, Delphi
Linux Mint Cinnamon  - Lazarus 1.8/FPC 3.0.4, NewPascal
Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB

fiazhnd

  • New member
  • *
  • Posts: 27
Re: insert and update query with same click event
« Reply #4 on: August 12, 2017, 04:53:29 pm »
I am using Delphi currently for project anyway if anyone can give me an example will great help.

shobits1

  • Sr. Member
  • ****
  • Posts: 278
  • .
Re: insert and update query with same click event
« Reply #5 on: August 12, 2017, 07:26:58 pm »
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.


 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus