MySQL Query – INSERT/UPDATE from Previous SELECT with Singularities

insertMySQLstored-proceduresupdate

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?

Best Answer

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";
Related Question