Jose Borges Jose Borges - 7 months ago 19
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

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.