4

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?
4

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";
  • 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? – ReynierPM Jun 29 '15 at 19:02
  • 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/… – oNare Jun 29 '15 at 19:33
  • Still not working for me, can you just edit your answer by adding the DELIMITERS part? – ReynierPM Jun 29 '15 at 19:37
  • I've done. Anyway, I'm not running MariaDB at this time to be sure. – oNare Jun 29 '15 at 19:40
  • It works fine, so tested on MariaDB. thx – ReynierPM Jun 29 '15 at 19:41
4

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.

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

  • This doesn't work I got this error: [Err] 1582 - Incorrect parameter count in the call to native function 'UCASE' – ReynierPM Jun 29 '15 at 19:00
  • 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. – Ravi Kumar Jun 30 '15 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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