My tables :
x
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'));
Advertisement
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')