0

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)

Assume that 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 work column).

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');

Of course, 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?

1 Answer 1

1

To avoid putting values two times in your INSERT statement use VALUES(<column_name>) function.

INSERT ... ON DUPLICATE KEY UPDATE Syntax
You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise.

Unfortunately it's not very clear in your question what is your desired outcome. But assuming that you want update only columns that have NULL or empty strings you can do

INSERT INTO some_table (f_name, l_name, work)
VALUES ('Dotan', '', 'php')
ON DUPLICATE KEY UPDATE
  f_name = COALESCE(NULLIF(f_name, ''), VALUES(f_name)),
  l_name = COALESCE(NULLIF(l_name, ''), VALUES(l_name)),
  work   = COALESCE(NULLIF(work, ''), VALUES(work));

Here is SQLFiddle demo

If it's not exactly what you want, please consider to post your desired outcome and explain what values under what conditions you want to update

1
  • @dotancohen You're quite welcome. Good luck :)
    – peterm
    Commented Dec 5, 2013 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.