Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Perfect, Peter. Thank you very much! –  dotancohen Dec 5 '13 at 9:38
    
@dotancohen You're quite welcome. Good luck :) –  peterm Dec 5 '13 at 9:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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