Mysql Question posted in
The official MySQL documentation can be found here.

MySQL 8 – how to block an update by NULL in a field, if it already has not null value?

Given: MySQL 8 and INSERT INTO … ON DUPLICATE KEY UPDATE query, which is applied to existing records in the table. It is necessary that if the existing value of the field is not NULL, and NULL is passed in the VALUES arguments,
then this NULL should not overwrite the existing value. At the same time, if 0 (not NULL) is passed, the existing field value must be overwritten to 0. The problem is that IF handles NULL and 0 the same way.

Example. The table with 3 columns good_id, price1, price2, the field good_id is the primary key.
There are already non-NULL values in this table and we are updating them.

INSERT INTO world.prices_history 
  (`good_id`, `p_date`, `price1`, `price2`)
VALUES 
   (1, 5.22, 6.11),
   (2, 5.52, 6.31),
   (3,  NULL, NULL),   
   (4,  0, 12) AS new_odku 
   (
     `odku_good_id`, `odku_price1`, `odku_price2`
   )
ON DUPLICATE KEY UPDATE
    `price1` = IF (`new_odku`.`odku_price1`, `new_odku`.`odku_price1`, `price1`),
    `price2` = IF (`new_odku`.`odku_price2`, `new_odku`.`odku_price2`, `price2`)

There will be no update for the good_id = 3 and 4. It’a OK with good_id=3, but not OK with good_id=4. I need that price1=0 is set to good_id=4.

IFNULL NOT WORKS, beacuse if field defined as "not null", and null given in VALUES, mysql converts NULL TO 0, and 0 is normal value for me, it can overwrite existing value.
(This sql query is a simple example, in reality it’s about developing a universal SQL query constructor that should do INSERT ON DUPLICATE KEY UPDATE query for any tables)

The only solution that I find – replace NULL in the price1 and price2 fields with some obviously unrealistic value (for example, -1), and compare odku_price1 and odku_price2 with -1:

INSERT INTO world.prices_history 
  (`good_id`, `p_date`, `price1`, `price2`)
VALUES 
   (1, 5.22, 6.11),
   (2, 5.52, 6.31),
   (3,  -1, -1),   
   (4,  0, 12) AS new_odku 
   (
     `odku_good_id`, `odku_price1`, `odku_price2`
   )
ON DUPLICATE KEY UPDATE
    `price1` = IF (`new_odku`.`odku_price1` <> -1, `new_odku`.`odku_price1`, `price1`),
    `price2` = IF (`new_odku`.`odku_price2` <> -1, `new_odku`.`odku_price2`, `price2`)

In this case, the old value will not be replaced for key 3, and 0 will be written for good_id=4.

My question is – is there a more beautiful solution to this problem?

Tags:

2

Answers


  1. 0

    use the IFNULL() construct MySQL documentation link

    This is first time answering, hope this helps

    Login or Signup to reply.
  2. 0 
    CREATE TRIGGER prices_history_trigger_bu
BEFORE UPDATE ON prices_history 
FOR EACH ROW
SET NEW.price1 = COALESCE(OLD.price1, NEW.price1)
  , NEW.price2 = COALESCE(OLD.price2, NEW.price2)
-- .....
    ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search