SQL check if existing row, if not insert and return it

Question

I'm having a problem with my sql query. I need to insert a data that needs to be checked first if it is existing or not. If the data is existing the sql query must return it, if not insert and return it. I already google it but the result is not quite suitable to my problem. I already read this.

Check if a row exists, otherwise insert How to 'insert if not exists' in MySQL?

Here is a query that' I'm thinking.

INSERT INTO @tablename(@field, @conditional_field, @field, @conditional_field) 
VALUES(
"value of field"
(SQL QUERY THAT CHECK IF THERE IS AN EXISTING DATA, IF NOT INSERT THE DATA and RETURN IT, IF YES return it),
"value of feild",
(SQL QUERY THAT CHECK IF THERE IS AN EXISTING DATA, IF NOT INSERT THE DATA and RETURN IT, IF YES return it)
);

Please take note that the conditional field is a required field so it can't be NULL.

Define what you mean with "return it" – Mark Rotteveel Oct 20 '14 at 11:25 — Mark Rotteveel, Oct 20 '14 at 11:25

score 1 · Answer 1 · answered Oct 30 '14 at 13:56

Your tag set is quite weird, I'm unsure you require all the technologies listed but as long as Firebird is concerned there's UPDATE OR INSERT (link) construction. The code could be like

UPDATE OR INSERT INTO aTable
  VALUES (...)
  MATCHING (ID, SomeColumn)
  RETURNING ID, SomeColumn

Note that this will only work for PK match, no complex logic available. If that's not an option, you could use EXECUTE BLOCK which has all the power of stored procedures but is executed as usual query. And you'll get into concurrent update error if two clients execute updates at one time.

score 0 · Answer 2 · answered Oct 20 '14 at 06:16

0

You could split it out into 2 steps 1. run a select statement to retrieve the rows that match your valus. select count (*) will give you the number of rows 2. If zero rows found, then run the insert to add the new values.

Alternatively, you could create a unique index form all your columns. If you try to insert a row where all the values exist, an error will be returned. You could then run a select statement to get the ID for this existing row. Otherwise, the insert will work.

answered Oct 20 '14 at 06:16

Kim Ryan

445
1
3
11

For some reason I can't split it out in to 2 steps. – Jaysser Balido Oct 20 '14 at 06:18
It is very hard to do that type of operation with a single SQL command. I would suggest you need to wrap your SQL within a simple script in a language such as Python or Perl or PHP. They have good libraries to connect to MySQL. You can then execute one statement, check the results and perform the second statement depending on the results of the first. – Kim Ryan Oct 20 '14 at 06:33
The stored procedure below looks like a good solution. But I still don't quite see what you are trying to achieve. If you want to stop duplicate values going into a table, a unique index is the best way do do that. It will raise a warning on insert. You already have all the values you are trying to insert, so there is no need to have them returned by the SQL statement. – Kim Ryan Oct 20 '14 at 22:45

score 0 · Answer 3 · answered Oct 20 '14 at 07:04

You can check with if exists(select count(*) from @tablename) to see if there is data, but with insert into you need to insert data for all columns, so if there is only @field missing, you cant insert values with insert into, you will need to update the table and go with a little different method. And im not sure, why do you check every row? You know for every row what is missing? Are you comparing with some other table?

score 0 · Answer 4 · answered Oct 20 '14 at 08:07

You can achieve it using MySQL stored procedure

Sample MySQL stored procedure

CREATE TABLE MyTable
    (`ID` int, `ConditionField` varchar(10))
;

INSERT INTO MyTable
    (`ID`, `ConditionField`)
VALUES
    (1, 'Condition1'),
    (1, 'Condition2')
;


CREATE PROCEDURE simpleproc (IN identifier INT,ConditionData varchar(10))
BEGIN


IF (SELECT ID FROM MyTable WHERE `ConditionField`=ConditionData) THEN
BEGIN
    SELECT * FROM MyTable WHERE `ConditionField`=ConditionData;
END;
ELSE
BEGIN
    INSERT INTO MyTable VALUES (identifier,ConditionData);
    SELECT * FROM MyTable WHERE `ConditionField`=ConditionData;
END;
END IF;

END//

To Call stored procedure

CALL simpleproc(3,'Condition3');

DEMO

SQL check if existing row, if not insert and return it

4 Answers4