[mysql] MySQL combine two columns and add into a new column

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.

SELECT CONCAT (zipcode, ' - ', city, ', ', state) AS COMBINED FROM TABLE

Are you sure you want to do this? In essence, you're duplicating the data that is in the three original columns. From that point on, you'll need to make sure that the data in the combined field matches the data in the first three columns. This is more overhead for your application, and other processes that update the system will need to understand the relationship.

If you need the data, why not select in when you need it? The SQL for selecting what would be in that field would be:

SELECT CONCAT(zipcode, ' - ', city, ', ', state) FROM Table;

This way, if the data in the fields changes, you don't have to update your combined field.

Add new column to your table and perfrom the query:

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