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 alwaysfalse
whenuser2=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
How to Check If Field Is Null or Empty in MySQL
How to Calculate Percentage Between Two Numbers Using SQL on Bigquery
Sql Query to Get the Employee Name and Their Manager Name from the Same Table
Find Records from One Table Which Don't Exist in Another
Select Distinct Records on a Join
Subtract Values from Two Columns in SQL Query
Deleting a Specific Character in a String from a Pattern
Sql 0 Results for 'Not In' and 'In' When Row Does Exist
Sql Query to Select Million Records Quickly
How to Select Three Table With Same Column Name But Different Values
Adding $ Dollar Sign on My Total Cost in SQL Server
Sql Query to Return Only First Occurance of One Column Value
Unioning Two Tables With Different Number of Columns
Checking If a SQL Server Login Already Exists
Sql Server-How to Replace a Date Column With a Current Date When the Column Has a Null Value
How to Subtract One Month from a Date Column
How to Combine Two Completely Different SQL Queries into One Result