Mysql Question posted in
The official MySQL documentation can be found here.

How to add a concatenated column to the table in MySQL?

I want to take my existing columns First_Name and Last_Name and concatenate them to add a new column Full_Name to the table Employees. (I am working on MySQL Workbench.)

So, I tried concatenating columns of First_Name and Last_Name using the following query:

SELECT CONCAT(First_Name, " ", Last_Name) AS Full_Name FROM Employees;

But this is only for display purpose so if I wanted to add this column into the table, how do I do it??

This query is also for displaying purpose and doesn’t actually include the new column into the table:

SELECT *, concat(First_name, " ", Last_name) AS Full_Name FROM Employees;

Tags:

3

Answers


  1. 0 
    CREATE TABLE New_Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Full_Name VARCHAR(255)
);

INSERT INTO New_Employees (First_Name, Last_Name, Full_Name)
SELECT First_Name, Last_Name, CONCAT(First_Name, ' ', Last_Name) AS Full_Name
FROM Employees;

    This query creates a new table called New_Employees with columns EmployeeID, First_Name, Last_Name, and Full_Name. Then, it inserts data from the existing Employees table into the new table, with the Full_Name column being the concatenation of First_Name and Last_Name. Adjust the data types and lengths as needed for your specific requirements.

    Login or Signup to reply.
  2. 0

    first you need to add column with Full_Name then update the Full_Name column

    ALTER TABLE Employees
ADD COLUMN Full_Name VARCHAR(100);

UPDATE Employees 
SET Full_Name = CONCAT(First_Name, " ", Last_Name);
    Login or Signup to reply.
  3. 0

    This is another way of adding a new column with values derived from other columns. The following generates the concatenated Full_Name column value and stores it.

    ALTER TABLE Employees 
  ADD COLUMN Full_Name VARCHAR(25) 
    GENERATED ALWAYS AS (CONCAT_WS(" ", First_Name, Last_Name)) STORED;

INSERT INTO 
  Employees (First_Name, Last_Name)
    VALUES ("John", "Doe");

SELECT First_Name, Last_Name, Full_Name FROM Employees;

+------------+-----------+-----------+
| First_Name | Last_Name | Full_Name |
+------------+-----------+-----------+
| John       | Doe       | John Doe  |
+------------+-----------+-----------+

    Note: The above ALTER TABLE also updates the Full_Name column values for the existing rows with the derived values.

    Reference: MySQL 8 – ALTER TABLE and Generated Columns

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search