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

Question

Using MySQL, I want to combine two columns in a table with an already-existing column name. As an illustration, I have several columns, including FIRSTNAME and LASTNAME. These two columns should be combined with the word FIRSTNAME alone.

So I tried this:

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

The two fields with the name FIRSTNAME are still visible. Normal values are present in one field while concatenated values are present in another. With those concatenated values, I just want one column. I have a table with more than 40 columns, however, I can only select single columns.

Can the original column be deleted using MySQL by itself?

Answer 1

There is no way to exclude a specific column from the * directive, as aziz-shaikh has noted, however, you might be able to apply the following workaround:

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

You should be able to address your customized FIRSTNAME column safely after doing this because the second instance of the FIRSTNAME column will take on the alias FIRSTNAME 1. Because * in any place other than the start will fail without aliasing, you must alias the table.