Looks like the extra formatting functions only work in conditionals and not in other substitutions. The below is tested and confirmed working.
Code:
UPDATE `#__comprofiler` SET `firstname` = CONCAT( UCASE( SUBSTR( `firstname`, 1, 1 ) ), LCASE( SUBSTR( `firstname` FROM 2 ) ) ), `middlename` = CONCAT( UCASE( SUBSTR( `middlename`, 1, 1 ) ), LCASE( SUBSTR( `middlename` FROM 2 ) ) ), `lastname` = CONCAT( UCASE( SUBSTR( `lastname`, 1, 1 ) ), LCASE( SUBSTR( `lastname` FROM 2 ) ) ) WHERE `id` = '[user_id]';
UPDATE `#__users` AS a LEFT JOIN `#__comprofiler` AS b ON a.`id` = b.`id` SET a.`name` = CONCAT_WS( ' ', b.`firstname`, b.`middlename`, b.`lastname` ) WHERE a.`id` = '[user_id]';
The above query will update first, middle, and last names in CBs table and change their first character to upper case. It'll then merge them together and update it in the Joomla users table.