how to concat two columns into one with the existing column name in mysql?

Question

I want to concatenate two columns in a table with a existing column name using mysql.

An example: I am having a column FIRSTNAME and LASTNAME and so many columns also. I want to concatenate these two columns with the name of FIRSTNAME only.

So I tried like this:

 SELECT *, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`;

but it displaying the two fields with the name of FIRSTNAME. one field is having normal values and another one is having concatenated values. I want only one column with those concatenate value. I can select single columns, but am having more than 40 columns in my table.

Is there any way to remove the original column using mysql itself?

Answer 1

As aziz-shaikh has pointed out, there is no way to suppress an individual column from the * directive, however you might be able to use the following hack:

SELECT CONCAT(c.FIRSTNAME, ',', c.LASTNAME) AS FIRSTNAME,
       c.*
FROM   `customer` c;

Doing this will cause the second occurrence of the FIRSTNAME column to adopt the alias FIRSTNAME_1 so you should be able to safely address your customised FIRSTNAME column. You need to alias the table because * in any position other than at the start will fail if not aliased.

Hope that helps!