INSERT/UPDATE in same query from previous SELECT with some singularities

Question

I'm trying to perform a INSERT|UPDATE by selecting data from another DB and this is what I have so far:

INSERT INTO pdone.reps 
 (veeva_rep_id,display_name,username,`first`,`last`,email) 
SELECT Id, CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)),username, firstname, lastname, email 
FROM veeva.`user` 
WHERE Id = "00580000003UB5VAAW"

• First problem, I got this error:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from veeva.user WHERE Id = "00580000003UB5VAAW"' at line 1

and I'm not sure what is wrong on the query, any advice?

• firstname could be JOHN or John or jOhn or any and I want to normalize as John, is UCASE(LEFT(firstname, 1) fine for this? The same apply to lastname
• If firstname is JOHN and lastname is DOE then username should be John Doe with a space between them, is my CONCATENATE right?
• I should insert some statics fields like one URL or just VEEVA how I can do that? Values are not present on the query shown here but is just add two more columns to the insert avatar_url and rep_type
• I am planning to add ON DUPLICATE KEY UPDATE but can be possible to add a restriction based on a column? Lets said UPDATE only if now> lastSyncDate?

Answer 1

I've made a function where you just put the string and the output will be in Capital Letters:

 DELIMITER //
 CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1
 DETERMINISTIC
 BEGIN
 DECLARE vPOSICION INT DEFAULT 0; 
 DECLARE vTMP VARCHAR(150) DEFAULT ''; 
 DECLARE vRESULTADO VARCHAR(150) DEFAULT '';
 DECLARE vCADENA VARCHAR(150) DEFAULT '';
 IF eCADENA IS NULL THEN
 SET vRESULTADO='';
 RETURN vRESULTADO;
 ELSE
 SET vCADENA=LCASE(eCADENA);
 REPEAT 
 SET vPOSICION=LOCATE(' ', vCADENA); 
 IF vPOSICION=0 THEN 
 SET vPOSICION=CHAR_LENGTH(vCADENA); 
 END IF; 
 SET vTMP=LEFT(vCADENA,vPOSICION); 
 IF CHAR_LENGTH(vTMP) < 4 THEN 
 SET vRESULTADO=CONCAT(vRESULTADO, vTMP); 
 ELSE 
 SET vRESULTADO=CONCAT(vRESULTADO, UPPER(LEFT(vTMP,1)),SUBSTRING(vTMP,2)); 
 END IF; 
 SET vCADENA=RIGHT(vCADENA,CHAR_LENGTH(vCADENA)-vPOSICION); 
 UNTIL CHAR_LENGTH(vCADENA) = 0 
 END REPEAT; 
 RETURN CONCAT(UPPER(LEFT(vRESULTADO,1)),MID(vRESULTADO,2,150)); 
 END IF;
 END
 //
 DELIMITER ;

Example:

 INSERT INTO pdone.reps 
 (veeva_rep_id,display_name,username,`first`,`last`,email) 
 SELECT 
 Id, 
 CONCAT(LCAPITAL(firstname),' ',LCAPITAL(lastname)) AS display_name,
 username, 
 firstname, 
 lastname, 
 email 
 FROM veeva.`user` 
 WHERE Id = "00580000003UB5VAAW";

• ReynierPM: I'm getting this error DECLARE vPOSICION INT DEFAULT 0; [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5 while try to execute the query for create the function, any advice?
• oNare: Mysql sees the ';' delimiters in the function and breaks your CREATE FUNCTION statement. To avoid this, change the delimiter before you define the function, and then change it back afterward: Like: DELIMITER // -- your create function definition statement here // DELIMITER ; As in your code the first ; semicolon was found at line 8, it tried to execute it the code up to the ';', and the syntax was invalid because it was incomplete (BEGIN without END). Source: stackoverflow.com/questions/22552472/…
• ReynierPM: Still not working for me, can you just edit your answer by adding the DELIMITERS part?
• oNare: I've done. Anyway, I'm not running MariaDB at this time to be sure.
• ReynierPM: It works fine, so tested on MariaDB. thx

Answer 2

Insert query:

INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type) 
SELECT 
 Id, 
 CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))),
 username, 
 firstname, 
 lastname, 
 email,
 'www.some_static_url.com',
 '1' 
FROM veeva.user 
WHERE Id = "00580000003UB5VAAW"

It will print Only first letter capital from both First & Last name.

Answer 3

CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1))

You missed one more closed parenthesis in your statement

CONCAT(UCASE(LEFT(firstname, 1)),UCASE(LEFT(lastname, 1)))

2.The query you wrote for concatenation returns only first letter in capital letters of both fist and last names and it doesn't print space between them.

example :First name:JOHN,Last name :DOE

• Result:JD

  so use following for concatenation

INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email)
SELECT
Id,
CONCAT(UCASE(LEFT(firstname, 1)),SUBSTRING(firstname,2),' ',UCASE(LEFT(firstname, 1)),SUBSTRING(firstname,2)), username,
firstname,
lastname,
email
FROM veeva.user
WHERE Id = '00580000003UB5VAAW';

• ReynierPM: This doesn't work I got this error: [Err] 1582 - Incorrect parameter count in the call to native function 'UCASE'
• Ravi Kumar: I for got add right parenthesis after second U CASE that's why it shows Incorrect parameter count error.now i updated query please use it.