How to Combine Columns Values Into a New Column in MySQL [All Method]️

How can we add a new column that depends on other columns in MySQL?

Suppose the current table in MySQL contains the following fields.

We want to create a new column fullName that contains both fields.

This concatenation can be done through a simple CONCAT operation.

fullName = CONCAT(firstName, ' ', lastName)

If we’re using MySQL v8.0, we can reference other table columns in our DEFAULT expression (as explained in the docs). If we’re using MySQL v5.7 or below, we’ll have to use something similar to the method explained here.

Create the new column

First, we want to add a new nullable column.

ALTER TABLE table_name ADD COLUMN fullName VARCHAR(100);

Populate the new column

Then, we want to populate this new column with the correct values.

UPDATE table_name SET fullName = CONCAT(firstName, ' ', lastName);

Update the new column for future values

Finally, we need to ensure all new inserts and updates of the old column update the new column.

We can add a trigger for all INSERT and UPDATE statements on this table.

Whenever a new row is inserted or updated on table_name, we will create or update the fullName column with the correct, concatenated value.

CREATE TRIGGER insert_trigger
BEFORE INSERT ON table_name
FOR EACH ROW
SET new.fullName = CONCAT(firstName, ' ', lastName);

CREATE TRIGGER update_trigger
BEFORE UPDATE ON table_name
FOR EACH ROW
SET new.fullName = CONCAT(firstName, ' ', lastName);