Consider a table of some family members:
mysql> SELECT * FROM some_table; +--------+--------+--------+ | f_name | l_name | work | +--------+--------+--------+ | Meirav | Cohen | school | | Dotan | Cohen | | +--------+--------+--------+ 2 rows in set (0.00 sec)
f_name is the primary key, and that all columns are
VARCHAR. Unknown data are not represented as NULL, but rather as an empty string (as can be seen in the
I would like to add some data that I know about a person, but I would prefer to not query the database to see what is there but rather have the database merge the extant value with the new values:
mysql> INSERT INTO some_table (f_name, l_name, work) VALUES ('Dotan', '', 'php') ON DUPLICATE KEY UPDATE f_name = IFNULL(f_name, 'Dotan'), l_name = IFNULL(l_name, ''), work = IFNULL(work, 'php');
IFNULL doesn't fit the job as it only checks for NULL, not for empty strings. Also, there is the disadvantage that each value to add must appear twice in the SQL.
Is there any way to have the database merge the data against empty strings? Also, can it be made such that the strings to insert appear only once in the SQL?