Adding a field with mySQL

Hello,

I'm trying to add a new field to an existing data set via mySQL (my first time doing this). The new field will take data from my existing data set (Product Name) and re-assign a new name leveraging a case statement.

Below is my code. I'm currently receiving an syntax error, which I'm looking for assistance on. Additionally, I want to validate this is the correct way to add a new field with mySQL integration.

CASE
WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
WHEN `Product Name` like '%AUDIO%' then 'Audio'
WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

ELSE unassigned
END AS new product name

Thanks!

Find more posts tagged with

Accepted answers

What does your entire transform look like?

If this is a single column in your transform, then it looks fine to me. In total, it should look something like

SELECT

...

, CASE WHEN [your case statement]

...

FROM

...

Based on the error, it seems the problem starts where your case statement begins, which is either preceded by a comma, separating it from a previous column, or the initial SELECT word.

lucywo

Hey there,

If you are working in the SQL environment, then check the codes below,

select a,

(CASE
WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
WHEN `Product Name` like '%AUDIO%' then 'Audio'
WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

ELSE 'unassigned'
END) as b

from c

Note: a - field you want to query; b - new field name; c - table name

If it still doesnt work, try change `Product Name` to [Product Name] or if even its possible to remove the blank space inside your field name.(`Product Name` --> Product_Name)

Thanks!

@lucywo

I think you'd be right on 1, but the OP said mySQL, not beast mode.

I added the same type consistency comment in my initial response also, with the option of using NULL instead, though my inquiry about wich was preferred was not answered.

All comments

So close.

Each possible output of your case statement needs to be the same data type. The word 'unassigned' is the issue here. Do you want it to say 'unassigned', or just not have a value? If you want it to say 'unassigned', that word needs to be in quotes like the other products are:

Pick one of the two in brackets:

CASE
WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
WHEN `Product Name` like '%AUDIO%' then 'Audio'
WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

ELSE [NULL or 'Unassigned']
END AS new product name

user07803

I'm now receiving the below error:

The database reported a syntax 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 'CASE WHEN `Product Name` like '%YOUTUBE%' then 'YouTube' WHEN `Product Name` lik' at line 1

Code leveraged is below (copied from your original reply)

CASE
WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
WHEN `Product Name` like '%AUDIO%' then 'Audio'
WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'
ELSE 'Unassigned'
END AS new product name

I hadn't seen it but your column alias "new product name" has to be one string, not three separate words.

END AS 'new product name'

END AS new_product_name

or something like it

Try that.

user07803

I gave that a shot and am still receiving the error from my previous reply.

What does your entire transform look like?

If this is a single column in your transform, then it looks fine to me. In total, it should look something like

SELECT

...

, CASE WHEN [your case statement]

...

FROM

...

Based on the error, it seems the problem starts where your case statement begins, which is either preceded by a comma, separating it from a previous column, or the initial SELECT word.

lucywo

Hey there,

If you are working in the SQL environment, then check the codes below,

select a,

(CASE
WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
WHEN `Product Name` like '%AUDIO%' then 'Audio'
WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

ELSE 'unassigned'
END) as b

from c

Note: a - field you want to query; b - new field name; c - table name

If it still doesnt work, try change `Product Name` to [Product Name] or if even its possible to remove the blank space inside your field name.(`Product Name` --> Product_Name)

Thanks!

@lucywo

I think you'd be right on 1, but the OP said mySQL, not beast mode.

I added the same type consistency comment in my initial response also, with the option of using NULL instead, though my inquiry about wich was preferred was not answered.

lucywo

@AS

Thanks for correcting me.

Yes your right. I have revised my answer.

Thanks again!