Jose Borges Jose Borges - 2 years ago 91
SQL Question

Merging 3 columns into 1 in MySQL

I'm having a problem to merge three columns into 1 in MySQL. It's true that I have some null values in some of the columns but I think it makes no difference since at first result it gives me the correct result, but when I browse the table, the 3 columns remain and the new column remains empty. This is the code I have used:

SELECT FirstName AS First_Name
, LastName AS Last_Name
, CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1, ContactPhoneNumber1) AS Contact_Phone
FROM TABLE1

Answer Source

One approach is to actually alter the table:

  1. In phpmyadmin or using an ALTER TABLE command, add the ContactPhone column
  2. UPDATE MyTable SET ContactPhone = CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1, ContactPhoneNumber1) to insert desired values (did you really mean to have ContactPhoneNumber1 twice in there?)
  3. And perhaps write a trigger so that when future rows are added, or existing rows have the area code or phone number changed, the ContactPhone column is automatically updated

But a much better approach is to create a VIEW on your TABLE that has this extra column:

CREATE VIEW MyVIEW AS SELECT *, CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1, ContactPhoneNumber1) AS ContactPhone from MyTable

Then you can use the view just like you used the table, but it has an extra synthesized column that never needs to be kept in sync.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download