Check If a Row Exists, Otherwise Insert

Update if row exists, otherwise insert new row into another table

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Check if a row exists Update Record, otherwise insert In Database in c++

Script #1. Check if record already exists:

SELECT COUNT(*) From <YourTable>
WHERE ID = 12345;

Script #2. If script #1 returns value >0 then record already exists. You do an update:

UPDATE <YourTable> SET 
Value1 = 'ABC',
Value2 = 'XYZ',
Value3 = 123
WHERE ID = 12345;

Script #2. If script #1 returns value =0 then record does not exists. You do insert:

INSERT INTO <YourTable> (ID,Value1,Value2,Value3) 
VALUES (12345,'ABC','XYZ',123);

Update a row if exists otherwise insert

You can

• add unique indices for user1 and user2 to improve speed and assure integrity.
• use Transaction to avoid collisions.

• combine the select and update query in one update:

  update table match 
  set user2 = 'U_2' 
  where ((user1 != 'U_2') AND (paired = false)) 
  LIMIT 1; 
  

• check if the update has affected rows. If not, insert the new row.

If i understand your intension properly, you can also:

• remove the column paired, it seems to be redundant, since it is always false when user2=null

mysql/mariadb, is it a bad idea to check if row exists by trying to insert a new row and checking for errors?

If the hashed message has to be unique, create a key on that column with the UNIQUE constrain: so there won't be two rows with the same hash.

Then, when you insert a new row modify your query with the following:

INSERT INTO table SET message='$message', hashed_message='$hashed_message'
ON DUPLICATE KEY id=id;

This will perform an insert if the hashed_message is unique. Otherwise will not do any update.

If you want to update something in case of duplicate your query will become:

INSERT INTO table SET message='$message', hashed_message='$hashed_message'
ON DUPLICATE KEY UPDATE message='$updated_message'

just to make an example.

Note that this method won't raise any exception in case of duplicate values: you need extra logic if you need to perform actions in your frontend in case of duplicates (i.e. message shown to the user).

More details here

Update row if exists or insert

In Oracle, you need to first parse, execute and then fetch before num_rows.

<?php

$stmt = oci_parse($DBC,$sqlSelect);

$rc = oci_execute($stmt);
$row=oci_fetch_array($stmt);
$jobExists=oci_num_rows($stmt)>0; 

   if($jobExists){
     $stmt = oci_parse($DBC,$SQL2);

     $rc = oci_execute($stmt);
      if (!$rc)
      {
      $error = oci_error($stmt);
      var_dump($error);
      }

      oci_free_statement($stmt);
   }
   else {
     $stmt1 = oci_parse($DBC,$SQL);
     $rc = oci_execute($stmt1);

      if (!$rc) {

        $error = oci_error($stmt1);
        var_dump($error);

      }

      oci_free_statement($stmt1);
   } 

?>

---

Related Topics