0

I know about insert ignore. That's of no use, because MariaDB does not consider NULL to be part of the uniqude index.

Therefore I have to use if ... else.

But this code does not run:

select *
from Nodes 
where `Key` = 'FAQPage';

if not found_row() then
   insert into Nodes (`Key`, ParentId)
   values ('FAQPage', null)
end if;

How can I use if ... else to conditionally insert data in MariaDB?

8
  • Check the row uniqueness by the trigger.
    – Akina
    Feb 5, 2022 at 11:22
  • @Akina, I didn't understand. This is a simple initial data script that I want to insert into a new database. Feb 5, 2022 at 12:24
  • What is unclear? in BEFORE INSERT trigger chech does the row with the values to be inserted already exists, and, if so, forbid the insertion.
    – Akina
    Feb 5, 2022 at 12:29
  • @Akina, this means that I should change the schema of my database, only because I want to insert a couple of rows as initial data. I don't think it's a good design. I prefer to be able to fix this if ... else issue. Feb 5, 2022 at 12:42
  • @Akina, maybe I misunderstand something here. A trigger as much as I know is something that you define for a table, and whenever a record is inserted/edited/deleted, you get to know and act accordingly or even prevent. If that's the case, then it's part of the schema. Feb 5, 2022 at 16:02

1 Answer 1

2
INSERT INTO Nodes (`Key`, ParentId)
SELECT @key, @parentid
WHERE NOT EXISTS ( SELECT NULL
                   FROM Nodes
                   WHERE `Key` <=> @key
                     AND ParentId <=> @parentid )

@key and @parentid are placeholders for the values to be inserted.

So if the row with the values to be inserted already exists then the subquery detects this, and none rows is inserted.

3
  • thank you so much. It worked. Interesting. A couple of things are new to me here. Is <=> equality check with data type? Also, why select null in the subquery? Feb 5, 2022 at 18:53
  • 1
    Is <=> equality check with data type? NULL-safe equal to operator why select null in the subquery? You can replace this NULL with any (really any) synthactically correct expression. It is ignored anycase, only its presence/existence is checked. Some people uses 0, 1, '' and so on... I like NULL.
    – Akina
    Feb 5, 2022 at 18:57
  • Thank you. You made my day. I had more than 5 hours of struggle with this. Feb 5, 2022 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.