Stack Exchange
Cancel Insert if inner query find nothing
I got the following query :
INSERT INTO contracts_settings (contract_id, setting_id, setting_value)
VALUES (:contract_id, (
    SELECT  setting_id
    FROM    settings
    WHERE   setting_type = :setting_type
        AND setting_name = :setting_name
    LIMIT   1
), :setting_value)
ON DUPLICATE KEY UPDATE setting_value = :setting_value

The value with the prefix : is replaced with data using PHP PDO::bindBalue.
If the inner query find nothing (it return NULL) but also INSERT a NULL statement. How to avoid that ?
Thanks.

Convert the INSERT ... VALUES syntax to INSERT ... SELECT:
INSERT INTO contracts_settings 
  (contract_id, setting_id, setting_value)
SELECT 
    :contract_id,
    setting_id,
    :setting_value
FROM  settings
WHERE setting_type = :setting_type
  AND setting_name = :setting_name
LIMIT   1

ON DUPLICATE KEY UPDATE 
    setting_value = :setting_value ;


If the SELECT return nothing, the query will fail ?

If the SELECT returns 0 rows, the INSERT will insert 0 rows (so, nothing).

Thanks you. Working like a charm ! You tought me something me sir !
