INSERT with clause SELECT WHERE NOT EXISTS

Question

I found in the Sopt a response to the same case that mine, but something unexpected happens because this is new to me, the SQL statement below returns an error while running.

$sql = "
INSERT INTO 
    `usuario_referencia`
(
`ade_user`,
`ade_ticket`,
`ade_status`
)
SELECT 
'CB15-1901',
'102030',
'A'
WHERE 
    NOT EXISTS 
    (
    SELECT 
        1 
    FROM 
        `usuario_referencia` 
    WHERE 
        `ade_user` = '102030' 
    AND 
        `ade_user` = 'CB15-1901'
    )
";

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 NOT EXISTS ( SELECT 1 FROM ' at line 12

The question is, in Mariadb the use of SELECT 1 is possible? if it is not that, what should be?

Answer 1

In his select is missing the from. Since you are generating data that is not in a table, you can use the FROM DUAL

SELECT 
'CB15-1901',
'102030',
'A'
FROM DUAL
WHERE 
    NOT EXISTS 
    (
    SELECT 
        1 
    FROM 
        `usuario_referencia` 
    WHERE 
        `ade_user` = '102030' 
    AND 
        `ade_user` = 'CB15-1901'
    )

DUAL is a fictitious table name, and as in Oracle is used in a SELECT which has no results from a specific table, and it is therefore necessary to FROM is mandatory for the select.

See here the DUAL working: SQL Fiddle

See the documentation here: MYSQL SELECT and MARIADB DUAL