Insert data into a column only if it has NULL value, else update it

Question

I want to update data into a column in a table only if the data sent from the HTML page is not NULL or ''. Else, that column must not be touched. Example :-

table_1

id      name         age        rent        
==========================================
1       Name 1         25         1000         
2       Name 2         28         NULL          
3       Name 3         35         1500          
4       Name 4         44         3200          
5       name 5          42        NULL

LET THE DATA SENT FROM HTML PAGE IS STOERED IN A VARIABLE today_rent AND THIS VARIABLE IS SENT TO MYSQL.

MySQL Query

IF(today_rent !=null OR today_rent != '') THEN
UPDATE table_1 SET rent=today_rent WHERE id=2
END IF;

Is there any other way to do it?

If that code (with the typo fixed) does what you want, I don't think there's a better way to do it. You can't combine those `INSERT` and `UPDATE` queries into a single query. — Barmar, Mar 18 '21 at 08:46
`NULL=NULL` is `NULL` which is not true. You probably mean `IF (rent IS NULL)`. — tadman, Mar 18 '21 at 08:51
@tadman That could be it. I need to try it out. And also , I think my messed up my question. I wanted to update that column only when **the user had some data sent from the HTML page**. Otherwise, it would not update that column. The problem is sometimes, even if there is no data, the column is updated as a blank space — Skumar, Mar 18 '21 at 09:14

score 1 · Answer 1 · answered Mar 18 '21 at 09:21

Add an index

CREATE UNIQUE INDEX idx ON test (id, (CASE WHEN rent IS NULL THEN 0 ELSE id END));

Then use

INSERT INTO test (id, rent) 
VALUES (@id,@new_rent) 
ON DUPLICATE KEY UPDATE rent = VALUES(rent);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c48a44a6268e2e1710162b337efa592b

PS. The query does not set name and age columns - but their setting necessity is not described in the question.

Insert data into a column only if it has NULL value, else update it

1 Answers1