Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

Question

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?

Answer 1

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) ;

Answer 2

Let's look at how this runs. (Keep in mind that the NOT EXISTS is in the SELECT.)

INSERT ...
    SELECT ...
    ON DUPLICATE KEY UPDATE ...

First, it performs the SELECT, which delivers some number of rows, possibly zero. (Note that the NOT EXISTS may cause it to deliver zero rows.)

Second, each of those rows (if any) is checked for duplicates in any UPDATE key. If no dup, it is inserted; if dup, then ON DUPLICATE KEY kicks in and it does an UPDATE.

Given that understanding, you should be able to answer your own question, plus any variants you have not yet mentioned.

Answer 3

I ended up creating the following trigger:

DECLARE paramvalue_ VARCHAR(50);
DECLARE msg VARCHAR(255);

CREATE TEMPORARY TABLE IF NOT EXISTS params_temp(extension integer, type varchar(50), subtype varchar(50), paramvalue varchar(50), active integer, PRIMARY KEY (`extension`,`type`,`subtype`), UNIQUE KEY `extension_type` (`extension`,`type`));
DELETE FROM params_temp;

INSERT INTO params_temp(extension, type, subtype, paramvalue, active) VALUES (NEW.extension, NEW.type, NEW.subtype, NEW.paramvalue, NEW.active);

INSERT INTO params_temp(extension, type, subtype, paramvalue, active)
SELECT extension, type, subtype, paramvalue, active FROM params_exceptions WHERE extension = NEW.extension AND subtype = NEW.subtype AND type = NEW.type
ON DUPLICATE KEY UPDATE paramvalue=params_exceptions.paramvalue;

SELECT paramvalue INTO paramvalue_ FROM params_exceptions WHERE extension = NEW.extension AND subtype = NEW.subtype AND type = NEW.type;
IF paramvalue_ != "" THEN
IF OLD.paramvalue != paramvalue_ THEN
  SET NEW.paramvalue = paramvalue_;
ELSE
    SET msg = "Update overriden.";
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
END IF;
END IF;