MySQL case makes a new column

Question

If I use a MySQL case statement, it creates a new column, it didn't replace the column.

Is it possible to replace the column instead of new column?

Example:

SELECT *, 
       CASE WHEN housenumber_addition IS NULL THEN 'ABC' 
       ELSE housenumber_addition END AS housenumber_addition 
FROM customer

Yes, it's possible -- don't use `SELECT *`, list columns explicitly instead. — mustaccio, Sep 23 '18 at 14:16

score 0 · Accepted Answer · answered Sep 23 '18 at 14:21

0

You select that column effectively twice, just like you can select any column a multiple times:

select housenumber_addition, housenumber_addition, housenumber_addition 
from customer

In general, you should select each column separately and replace housenumber_addition by the code you provided. SELECT * is considered harmful anyways.

answered Sep 23 '18 at 14:21

steffen

16,138
4
42
81

I'm not sure this directly addresses the OP, which is concerned with _not_ inadvertently selecting the `hostnumber_addition` column in _addition_ to the `CASE` expression. – Tim Biegeleisen Sep 23 '18 at 14:27
Thanks! I tried the first time with: `SELECT housenumer_addition, CASE WHEN housenumber_addition IS NULL THEN 'ABC' ELSE housenumber_addition END AS housenumber_addition FROM customer` And got the old and new column. My bad.. – Mirkin Sep 23 '18 at 14:27

score 0 · Answer 2 · answered Sep 23 '18 at 14:28

Just explicitly select the columns you want to appear in your result set, and, in your case, exclude the housenumber_addition column:

SELECT
    col1,     -- some column, not housenumber_addition
    col2,     -- some other column, also not housenumber_addition
    ...,
    CASE WHEN housenumber_addition IS NULL
         THEN 'ABC' 
         ELSE housenumber_addition END AS housenumber_addition
FROM customer;

Note that your CASE expression doesn't serve much purpose, because you could have just used COALESCE:

COALESCE(housenumber_addition, 'ABC')

MySQL case makes a new column

2 Answers2