Make a conditional UPDATE on the ON DUPLICATE KEY

Question

Make a conditional UPDATE on the ON DUPLICATE KEY

Navigation

#1 by (5 votes)
#2 by (2 votes)

4

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

If position is the same it updates the fields. I need to verify that position is different from 1 ( status ) in order to update.

This status <> 1 is not passed by .csv is only registered in the database and I can not update the records that are with status .

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

php mysql

asked by anonymous 19.11.2014 / 03:18

2 answers

2

As the 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 into 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 procedure:

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 triggers the ON DUPLICATE KEY is position , if it is not, just fit in SELECT .

Remember to update the types of fields, I just put as an example.

19.11.2014 / 03:22

Upload template automatically Know line and file that is executing a method

score 5 · Accepted Answer

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: You do not need to check if position field is equal to what you are inserting if it has CONSTRAINT UNIQUE as you commented in @Bacco's response, since ON DUPLICATE KEY UPDATE will run only when you have a line of INSERT with a position that already exists.

Reference:
link