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

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?

Answers


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!


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;

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.


You can try this simple way for combining columns:

select some_other_column,first_name || ' ' || last_name AS First_name from customer;

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

Need Your Help

The Google Maps API server rejected your request

javascript google-maps google-maps-api-3

I m working on an application which renders google maps on an image as below

Scope in javascript acting weird

javascript function scope pass-by-reference

Object are passed with their reference in javascript. Meaning change in that object from any where should be reflected.