Mysql Syntax Error with CONCATs

Question

Hey guys I'm getting a syntax error on my CONCATS for some reason, this is very weird since its pretty much a copy/paste from another one of my queries using CONCATS that works flawlessly?

Here's the bad query

SELECT 
    brand, 
    case_count AS case, 
    variety, 
    style, 
    grower_lot AS lot, 
    pack_date AS date, 
    CONCAT(berry_size1, "-", berry_size2, "/", berry_size3, "-", berry_size4) AS size, 
    CONCAT(color1, "-", color2) AS color, 
    CONCAT(stem1, "-", stem2, "-", stem3) AS stem_cndt, 
    CONCAT(bunch_count1, "-", bunch_count2, "-", bunch_count3) AS bnch_cnt, 
    CONCAT(shatter1, "-", shatter2) AS shatter, 
    CONCAT(splits1, "-", splits2) AS split, 
    CONCAT(decay_count1, "-", decay_count2) AS decay, 
    CONCAT(wet_sticky1, "-", wet_sticky2) AS wet_sticky, 
    overall_quality AS quality, 
    CONCAT(sugar_brix1, "-", sugar_brix2) brix, 
    overall_condition AS condition, 
    rating, 
    inspector AS insp, 
    comments 
FROM `lot`

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case, variety, style, grower_lot AS lot, pack_date AS date, ' at line 3

And here's one that seems to work

SELECT
        shipper, 
        po, 
        commodity as comm, 
        count, 
        size, 
        label, 
        variety, 
        pack_date AS date, 
        grower_lot AS lot, 
        CONCAT(color1, "-", color2) AS color, 
        CONCAT(sizing1, "-", sizing2, " / ", "sizing3", "-", sizing4 ) AS size, 
        CONCAT(firmness1, "-", firmness2) AS firmness, 
        CONCAT(scars_count1, "-", scars_count2) AS scars, 
        CONCAT(bruise_count2, "-", bruise_count2) AS bruise, 
        CONCAT(decay_count1, "-", decay_count2) AS decay_cnt, 
        CONCAT(sugar_brix1, "-", sugar_brix2) brix, 
        rating, 
        inspector AS insp, 
        comments
FROM `berries`

Please paste the error message.

Matt Fenwick
– Matt Fenwick

2012-03-01 17:57:00 +00:00
Commented Mar 1, 2012 at 17:57 — Matt Fenwick
– Matt Fenwick, Commented Mar 1, 2012 at 17:57

Tess Rosania · Accepted Answer · 2012-03-01 17:58:48Z

4

Not an issue with CONCAT per se, but case is a reserved word in MySQL, and needs to be backtick-escaped.

answered Mar 1, 2012 at 17:58

Tess Rosania

10.1k2 gold badges22 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ehime Over a year ago

After changing case to case_cnt I get the error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition, rating, inspector AS insp, comments FROM lot LIMIT 0, ' at line 18

Tess Rosania Over a year ago

Unfortunately, condition is another reserved word. @Mchl's response links to the full list of reserved words.

ehime Over a year ago

Yep that was it, just seem to be using all the reserved words today =P

Mchl · Accepted Answer · 2012-03-01 17:59:41Z

1

case is MySQL's reserved word. You need to put it within backticks, if you want to use it as column alias.

Full list of reserved words available here: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

answered Mar 1, 2012 at 17:59

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Collectives™ on Stack Overflow

Mysql Syntax Error with CONCATs

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related