Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script.

INSERT INTO params (extension, type, subtype, paramvalue)
VALUES ('12345', 'FRNR1', 'FORWARD', '24268')
ON DUPLICATE KEY UPDATE paramvalue = VALUES(paramvalue);


The purpose is that params_exceptions contains records which prevents certain records in params to be updated with the new data (or using the data in params_exceptions afterwards for that record).

I've been messing around a bit and I was thinking of combining WHERE NOT EXISTS within the ON DUPLICATE KEY UPDATE? Of course all of the above is possible by using sequential statements, but it's possible that the script would run at the same time and I can't use transactions for now.

I was thinking of the following statement, but I can't seem to figure it out:

INSERT INTO params (extension, type, subtype, paramvalue)
SELECT *
FROM (SELECT '12345', 'TEST', 'TEST2', '24268') AS tmp
WHERE NOT EXISTS (
    SELECT 1, extension
    FROM params_exceptions t
    WHERE extension = '12345' AND
          type = 'FRNR1' AND
          subtype = 'FORWARD'
) LIMIT 1
ON DUPLICATE KEY UPDATE subtype=subtype, paramvalue=t.extension;


Can someone give me some advice here?
The problem in your query is this part: 

ON DUPLICATE KEY UPDATE 
    --
    paramvalue = t.extension ;


The alias t is not visible in that part of the query.

However, assuming that the two values, the one to be inserted in the params.extension column (12345) and the one checked against the params_extensions column (WHERE t.extension = '12345')  are the same, then you you can use the VALUES (extension) in the UPDATE part:

Several other improvements can be made:


no need for a derived table (tmp), you can use the internal dual table.
the UPDATE subtype = subtype is redundant
the inetger values (eg. 12345) need not to be passed quoted. They are not string literals, they are integer values.


The statement becomes:

INSERT INTO params 
  (extension, type, subtype, paramvalue) 
SELECT 1234567, 'TEST', 'TEST2', '24268'
FROM dual 
WHERE NOT EXISTS 
  ( SELECT 1  
    FROM params_exceptions t     
    WHERE extension = 1234567
      AND type = 'FRNR1' 
      AND subtype = 'FORWARD' 
  ) 
ON DUPLICATE KEY UPDATE 
  paramvalue = VALUES (extension) ;