Getting errors with my mariadb-10.3 version

Question

I'm looking for a function that insert data if they are not existing.

I tried with IF not exists but I think that my mariadb version is no up enought to handle this.

So I'm trying with NOT IN, but get the same issue...

CREATE DEFINER=`root`@`localhost` PROCEDURE `close_ticket`(IN idT INT,IN accuse VARCHAR(45),IN lienAccuse VARCHAR(60), IN lienG VARCHAR(60),IN jours INT,IN reason VARCHAR(250), IN isclosed BOOLEAN)
BEGIN
    INSERT INTO bot_onet.accuse (`Pseudo`,`lienFaceit`) SELECT accuse,lienAccuse
        WHERE lienAccuse NOT IN(SELECT `lienFaceit` FROM `bot_onet`.`accuse` );
    SELECT @ID :=  `idAccuse` FROM bot_onet.accuse WHERE `lienFaceit` = lienAccuse;
    INSERT INTO bot_onet.ticket_has_accuse (`Ticket_idTicket`,`Accuse_idAccuse`,`duree`,`timecode`,`unbaned`,`raison`) VALUES (idT,@ID,jours,unix_timestamp(),FALSE,reason) ;
    UPDATE bot_onet.ticket SET `Fermeture` = unix_timestamp(),`closed` = isclosed, `LienGame` = LienG WHERE idTicket = idT;
END

Piece of code which create the bug

INSERT INTO `bot_onet`.`Accuse` (`Pseudo`,`lienFaceit`) SELECT 'C0casio45','test' 
WHERE 'C0casio45' NOT IN(SELECT `Pseudo` FROM `bot_onet`.`Accuse`)

error code

09:52:12    
INSERT INTO bot_onet.Accuse (`Pseudo`,`lienFaceit`) SELECT 'test','faceit/test'   
WHERE lienAccuse NOT IN(SELECT lienAccuse FROM bot_onet.Accuse )    Error Code: 1064. 
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 
'WHERE lienAccuse NOT IN(SELECT lienAccuse FROM bot_onet.Accuse )' at line 2    
0.110 sec

And if i use the from statement, it returns no error but he is not insert data too

INSERT INTO `bot_onet`.`Accuse` (`Pseudo`,`lienFaceit`) SELECT 'C0casio45','test' 
FROM `bot_onet`.`Accuse` WHERE 'C0casio45' NOT IN(SELECT `Pseudo` FROM `bot_onet`.`Accuse`)

*Getting errors* ... *get the same issue* - provide **complete and unchanged** error message. PS. Do you remember about DELIMITER? — Akina, Oct 13 '21 at 08:45
`SELECT lienAccuse FROM bot_onet.accuse` - in this subquery `lienAccuse` is SP parameter (local variable), not a column from the table. You must specify table alias or rename parameters in this and all another places. — Akina, Oct 13 '21 at 08:48
Actually, delimiter works, i tried to do the insert statement alone and he doesn't work too — C0casio45, Oct 13 '21 at 08:49
`INSERT INTO bot_onet.accuse (`Pseudo`,`lienFaceit`) SELECT accuse,lienAccuse WHERE lienAccuse NOT IN(SELECT lienAccuse FROM bot_onet.accuse );` - FROM is lost... — Akina, Oct 13 '21 at 08:50
Do not use SELECT into UDV then INSERT .. VALUES, use solid INSERT .. SELECT. — Akina, Oct 13 '21 at 08:51
I just update the code, i got some errors that i forget to resolve before updating him — C0casio45, Oct 13 '21 at 08:53
Error message tells that FROM clause is absent. Server waits for FROM but finds WHERE. — Akina, Oct 13 '21 at 09:01
See the answer - the place is marked with according comment. — Akina, Oct 13 '21 at 09:08

score 0 · Accepted Answer · answered Oct 13 '21 at 09:02

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `close_ticket`(IN idT INT,
                         IN accuse VARCHAR(45),
                         IN lienAccuse VARCHAR(60), 
                         IN lienG VARCHAR(60),
                         IN jours INT,
                         IN reason VARCHAR(250), 
                         IN isclosed BOOLEAN)
BEGIN
    INSERT INTO bot_onet.accuse (`Pseudo`,`lienFaceit`) 
    SELECT accuse,              -- IN accuse VARCHAR(45) is used
                  lienAccuse    -- IN lienAccuse VARCHAR(60) is used
-- FROM clause is lost
    WHERE lienAccuse            -- IN lienAccuse VARCHAR(60) is used
                     NOT IN( SELECT `lienFaceit` 
                             FROM `bot_onet`.`accuse` );
    SELECT @ID := `idAccuse` 
    FROM bot_onet.accuse 
    WHERE `lienFaceit` = lienAccuse;   -- IN lienAccuse VARCHAR(60) is used
    INSERT INTO bot_onet.ticket_has_accuse (`Ticket_idTicket`,`Accuse_idAccuse`,`duree`,`timecode`,`unbaned`,`raison`) 
    VALUES (idT,@ID,jours,unix_timestamp(),FALSE,reason);  -- idT,jours and reason SP parameters are used
    UPDATE bot_onet.ticket 
    SET `Fermeture` = unix_timestamp(),
        `closed` = isclosed, 
        `LienGame` = LienG 
    WHERE idTicket = idT;   -- IN idT INT is used
END

error code

Error message tells that FROM clause is absent.

Yea i see, but i need to specify a table, but in my case, data who are set came from my prepared statement, if i put a FROM it will take the column in the table. Anyway, the command didn't create anything, in the prepared command and out — C0casio45, Oct 14 '21 at 07:03
@C0casio45 If you need to insert constant values then use INSERT .. VALUES, not INSERT .. SELECT. If you need to perform this only when some condition is met then use external IF (IF .. THEN INSERT .. VALUES (..) END IF). — Akina, Oct 14 '21 at 07:07

Getting errors with my mariadb-10.3 version

1 Answers1