IF NOT EXISTS query used to insert new rows throws error

Question

I'm trying to use this query to insert the value kyckling into column Produkter and Sallader into column typ, but I get an error around IF NOT EXISTS:

BEGIN
    IF NOT EXISTS (SELECT * 
                   FROM innehall
                   WHERE Produkter = 'kyckling'
                     AND typ = 'Sallader')
    BEGIN
        INSERT INTO innehall (Produkter, typ)
        VALUES ('kyckling', 'Sallader')
    END
END;

Hey, check these: https://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table & https://stackoverflow.com/questions/22290130/if-not-exists-sql-statement-giving-error — Junkrat, Jun 01 '19 at 20:08

score 0 · Answer 1 · answered Jun 01 '19 at 21:08

I think the problem is that control-flow logic such as the IF statement (NOT if() the function) is allowed only in programming blocks -- stored procedures, functions, and trigger.

You can do what you want with a query such as:

INSERT INTO innehall (Produkter, typ)
    SELECT Produkter, typ
    FROM (SELECT 'kyckling' as Produkter, 'Sallader' as typ) x
    WHERE NOT EXISTS (SELECT 1
                      FROM innehall i
                      WHERE i.Produkter = x.Produkter AND i.typ = x.typ
                     );

But the better approach is to let the database ensure uniqueness rather than you having to do this. So:

alter table innehall add constraint unq_innehall_produkter_typ
    unique (produkter, typ);

And then phrase the insert like this:

insert into innehall (produkter, typ)
    values (kyckling', 'Sallader')
    on duplicate key update typ = values(typ);

This attempts the update. If the unique constraint is violated, then it ignores the error.

IF NOT EXISTS query used to insert new rows throws error

1 Answers1