how to concat two columns into one with the existing column name in mysql?
Instead of getting all the table columns using * in your sql statement, you use to specify the table columns you need.
You can use the SQL statement something like:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS FIRSTNAME FROM customer;
BTW, why couldn't you use FullName instead of FirstName? Like this:
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS 'CUSTOMER NAME' FROM customer;
You can try this simple way for combining columns:
select some_other_column,first_name || ' ' || last_name AS First_name from customer;
Remove the *
from your query and use individual column names, like this:
SELECT SOME_OTHER_COLUMN, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`;
Using *
means, in your results you want all the columns of the table. In your case *
will also include FIRSTNAME
. You are then concatenating some columns and using alias of FIRSTNAME
. This creates 2 columns with same name.
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!