There is a table of the form id | p1 | p2 | p3 id | p1 | p2 | p3 id | p1 | p2 | p3 , where id is the primary key.

I update several lines at once with a query:

 INSERT INTO `table` (id, p1, p2, p3) VALUES (1, 0.1, 0.2, 0.3), (2, 0.1, NULL, 0.3), (3, NULL, 0.2, 0.3) ON DUPLICATE KEY UPDATE p1=VALUES(`p1`), p2=VALUES(`p2`), p3=VALUES(`p3`)

Question: how to change the query so that the NULL values ​​are ignored and the old values ​​remain in the database? Somehow type p1=VALUES(p1) IF NOT NULL , I can not figure out.

    1 answer 1

    Try using the IFNULL function.

     INSERT INTO `table` (id, p1, p2, p3) VALUES (1, 0.1, 0.2, 0.3), (2, 0.1, NULL, 0.3), (3, NULL, 0.2, 0.3) ON DUPLICATE KEY UPDATE p1=IFNULL(VALUES(`p1`), p1), p2=IFNULL(VALUES(`p2`), p2), p3=IFNULL(VALUES(`p3`), p3)

    This function returns the first argument if it is not NULL, otherwise returns the second argument.