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

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'));

CodePudding user response：

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')

CodePudding user response：

Here's two ways to insert only when it doesn't exist already in the target table.

The first is procedural, with an insert from select.

SET @FirstLabel = 'Str Test';
SET @SecondLabel = 'Str Match';
IF NOT EXISTS (SELECT 1 from FirstTable WHERE label = @FirstLabel) 
THEN 
    INSERT INTO FirstTable (label, secondTable_Id) 
    SELECT @FirstLabel, id 
    FROM SecondTable 
    WHERE label = @SecondLabel
    ORDER BY id DESC
    LIMIT 1;
END IF;

The second is an insert from a select with a NOT EXISTS.

SET @FirstLabel = 'Str Test';
SET @SecondLabel = 'Str Match';
INSERT INTO FirstTable (secondTable_Id, label) 
SELECT t2.id, @FirstLabel
FROM SecondTable t2
WHERE t2.label = @SecondLabel
  AND NOT EXISTS (
     SELECT 1
     FROM FirstTable t1
     WHERE t1.secondTable_Id = t2.id
       AND t1.label = @FirstLabel
  )
ORDER BY t2.id DESC
LIMIT 1;

db<>fiddle here