By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id INT, name VARCHAR(255), age INT, rent INT);
INSERT INTO test VALUES
(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);
SELECT * FROM test;
Records: 5 Duplicates: 0 Warnings: 0
| 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 |
CREATE UNIQUE INDEX idx ON test (id, (CASE WHEN rent IS NULL THEN 0 ELSE id END));
Records: 5 Duplicates: 0 Warnings: 0
INSERT INTO test (id, rent) VALUES (1,1111) ON DUPLICATE KEY UPDATE rent = VALUES(rent);
INSERT INTO test (id, rent) VALUES (2,2222) ON DUPLICATE KEY UPDATE rent = VALUES(rent);
INSERT INTO test (id, rent) VALUES (3,NULL) ON DUPLICATE KEY UPDATE rent = VALUES(rent);
SELECT * FROM test;
| id | name | age | rent |
|---|---|---|---|
| 1 | Name 1 | 25 | 1111 |
| 2 | Name 2 | 28 | null |
| 3 | Name 3 | 35 | 1500 |
| 4 | Name 4 | 44 | 3200 |
| 5 | name 5 | 42 | null |
| 2 | null | null | 2222 |
| 3 | null | null | null |