MySQL 8.0

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

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

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