CODEWITHSUNDEEP
mysqlsql
Dónal
Dónal

Reputation: 187499

INSERT statement that checks if an equivalent row already exists

In a MySQL database I have a table test that has two columns foo and bar. I'm trying to write a single SQL statement that insert a row only if such a row doesn't already exist. I've tried the following, but it doesn't work if the table is empty:

INSERT INTO test (foo, bar) 
SELECT 'foo', 'bar'
FROM test
WHERE NOT EXISTS  
(SELECT 1 FROM test WHERE foo = 'foo' AND bar = 'bar')
LIMIT 1;

Upvotes: 2

Views: 1255

Answers (5)

pete
pete

Reputation: 25081

Have you tried something like:

SELECT      'foo'
            , 'bar'
WHERE       1 NOT IN  (
                SELECT  CASE WHEN COUNT(*) < 1
                            THEN 0
                            ELSE 1
                        END
                FROM    test
                WHERE   foo = 'foo'
                        AND bar = 'bar'
            )

Note the removal of LIMIT 1 (I don't think the LIMIT 1 is hurting, but I don't think it's helping either as the clause was outside the subquery).

Please note the above is tested and works nicely for empty tables.

Also, for the above IN is required, not EXISTS as EXISTS only returns true if a non-null set is returned (or a null-set for NOT EXISTS).

Upvotes: 1

GarethD
GarethD

Reputation: 69749

It is not necessary for the EXISTS clause to reference the table you are selecting from, and since you are only using outer references in the SELECT for the insert statement you can use any table to select from, in fact you don't even need to use a concrete table e.g.

INSERT INTO Test
SELECT  'foo', 'bar'
FROM    DUAL
WHERE   NOT EXISTS (SELECT 1 FROM test WHERE foo = 'foo' AND bar = 'bar');

Example SQL Fiddle

Upvotes: 2

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

The test table is empty, so no record will come in Select. So use Dual table with Select.

    INSERT INTO test (foo, bar) 
    SELECT 'foo', 
           'bar' 
    From DUAL
    WHERE  NOT EXISTS (SELECT 1 
                       FROM   test  
                       WHERE  foo = 'foo' 
                              AND bar = 'bar')

DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.

Upvotes: 2

Sebas
Sebas

Reputation: 21522

I don't think you can insert a resultset from the same table you're inserting AND checking if the row exists.

Anyway, if not, here is a workaround I guess:

INSERT INTO test t1 (foo, bar) 
ON DUPLICATE KEY update t1.foo=t2.foo;

EDIT: In your original query I thought you were doing an insert from a select statement. Apparently you just want to insert a given manual tuple, so I changed my suggestion. It should work even if the table is empty.

Upvotes: 0

trapper
trapper

Reputation: 11993

Could possibly use REPLACE INTO which will replace the existing record or create new if none exists. Depends on the specifics of your content though if this approach would work.

Upvotes: -1

Related Questions