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

User · Answer

Just Remove   from your select clause  and mention all column names explicitly and omit the FIRSTNAME column  After this write CONCAT FIRSTNAME       LASTNAME  AS FIRSTNAME  The above query will give you the only one FIRSTNAME column

User · Answer

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

User · Answer

I am a novice and I did it this way    Create table Name1   F Name varchar 20   L Name varchar 20   Age INTEGER    Insert into Name1 Values   Tom    Bombadil   32     Danny    Fartman   43     Stephine    Belchlord   33     Corry    Smallpants   95  Go  Update Name1 Set F Name   CONCAT F Name       L Name  Go  Alter Table Name1 Drop column L Name Go  Update Table Name Set F Name

User · Answer

You can try this simple way for combining columns   select some other column first name           last name AS First name from customer

User · Answer

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

User · Answer

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

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

Examples related to mysql