How insert if a row not already exists ? (query)

My tables :

FirstTable
--------------------
'id' INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
'label' VARCHAR(255) UNIQUE NOT NULL
'secondTable_id' INT(11) UNIQUE NOT NULL


SecondTable
--------------------
'id' INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT
'label' VARCHAR(255) UNIQUE NOT NULL

Wrong migration script :

IF ('Str Test' NOT IN (SELECT label from FirstTable)) THEN 
    INSERT INTO FirstTable
    (label, secondTable_Id) VALUES 
    ('Str Test', (SELECT id FROM SecondTable WHERE label = 'Str Match'));
END IF;

I try insert new row if FirstTable.label == Str Test isn’t already exists but i get an error :

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ” at line 4

Update : I know IF/ELSE can only use in procedure/function but I hope we can do something like. Any help will be much appreciate ❤️

‘Paul T.’ Answer : If values can throws SQL errors at insertion (like unique or foreign keys), you can use INSERT IGNORE like bellow.

INSERT IGNORE INTO FirstTable
    (label, SecondTable_id) VALUES 
    ('Str Test', (SELECT id FROM SecondTable WHERE label = 'Str Match'));

Answer

INSERT INTO FirstTable(label, secondTable_Id)
SELECT 'Str Test', id 
FROM SecondTable 
WHERE label = 'Str Match' and not exists(
                            select * 
                            from FirstTable 
                            where label = 'Str Test')

Advertisement

Answer