MySQL combine two columns and add into a new column

Question

I have the following structure with a MySQL table:

+----------------+----------------+----------+ |    zipcode     |      city      |   state  | +----------------+----------------+----------+ |     10954      |     Nanuet     |    NY    | +----------------+----------------+----------+ 

I want to combine the above 3 columns into one column like this:

+---------------------+ |      combined       | +---------------------+ | 10954 - Nanuet, NY  | +---------------------+ 

And I want to add this "combined" column to the end of the table without destroying the original 3 fields.

Answer 1

Create the column:

ALTER TABLE yourtable ADD COLUMN combined VARCHAR(50); 

Update the current values:

UPDATE yourtable SET combined = CONCAT(zipcode, ' - ', city, ', ', state); 

Update all future values automatically:

CREATE TRIGGER insert_trigger BEFORE INSERT ON yourtable FOR EACH ROW SET new.combined = CONCAT(new.zipcode, ' - ', new.city, ', ', new.state);  CREATE TRIGGER update_trigger BEFORE UPDATE ON yourtable FOR EACH ROW SET new.combined = CONCAT(new.zipcode, ' - ', new.city, ', ', new.state);