How to Combine Columns Values Into a New Column in MySQL
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
fullName = CONCAT(firstName, ' ', lastName)
If we’re using
MySQL v8.0, we can reference other table columns in our
DEFAULTexpression (as explained in the docs). If we’re using
MySQL v5.7or 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
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);
More SQL Articles
- How to Format a Number with Two Decimal Places in SQLite
- How to Order By Multiple Columns in SQL
- How to Count the Number of Rows with the Same Value
- How to Order a SQL String Column By Its Numeric Value
- How to Check if a Column is Null in MySQL
- How to Drop Multiple Columns with ALTER TABLE in SQL
- What Stripe Data Do I Store in My Database?
- How To Query JSON Object with Unknown Keys in PostgreSQL
- How To Convert an Escaped JSON String in PostgreSQL to a JSON Object