Make a conditional update on ON DUPLICATE KEY

Question

I have a query that takes the data from a . csv and inserts the data into the table.

If the position is the same it updates the fields. I need that in addition to checking the position the query check whether status is different from 1 (status <> 1) to update.

That one status is not passed by . csv is only registered in the database and cannot update the records that are with status = 1.

How to do this?

$import=
       "INSERT INTO registros (NAME, position, price)
        VALUES
        (
         '$data[1]',
         '$data[2]',
         '$data[3]'
        ) ON DUPLICATE KEY UPDATE position = position,
        NAME = '$data[1]',
        position = '$data[2]',
        price = '$data[3]',
        qty_try = 0";

Answer 1

Use the function IF()

INSERT INTO registros (NAME, position, price)
    VALUES
    (
     '$data[1]',
     '$data[2]',
     '$data[3]'
    ) ON DUPLICATE KEY UPDATE 
            /* SE  status atual for diferente de 1
               quando tivermos um position que 
               já existe     ,                           SE TRUE, SE FALSE */
    NAME     = IF(status <> 1,                        '$data[1]',     NAME),
    price    = IF(status <> 1,                        '$data[3]',    price),
    qty_try  = IF(status <> 1,                                 0,  qty_try);  

Note: No need to check if field position is the same as what is being inserted if it has a CONSTRAINT UNIQUE as you commented in @Bacco’s reply, since the ON DUPLICATE KEY UPDATE will be executed only when you have a INSERT with a position that already exists.

Reference:
http://thewebfellas.com/blog/2010/1/18/conditional-duplicate-key-updates-with-mysql

Answer 2

As ON DUPLICATE KEY does not allow conditional situations **, perhaps the best solution is a stored Procedure:

_{** but see an outline for this limit in the @Thomas response, which transfers the condition to within the parameters, which gives the same effect.}

With stored Procedure, in PHP you do this:

$import = "CALL updateCustomizado( '$data[1]', '$data[2]', '$data[3]' );";

And in Mysql you create the database:

DELIMITER //
CREATE PROCEDURE `updateCustomizado`(xname VARCHAR(20), xposition INT, xprice DECIMAL)
BEGIN
   IF EXISTS (SELECT * FROM registros WHERE `position` = xposition) THEN
      UPDATE registros
      SET name=xname, price=xprice
      WHERE `position` = xposition AND status <> 1;
   ELSE
      INSERT INTO registros (name,position,price) VALUES ( xname, xposition, xprice);
   END IF;
END //
DELIMITER ;

I’m assuming that your key that fires the ON DUPLICATE KEY is the position, if it is not, just fit in the SELECT.

Remember to update the field types, I put only as an example.