MySQL – How to Merge Data from One Column to Another

MySQL

I have a table named my_jobs that I am trying to copy a column named "location" into two other columns called "city" and "state." The city and state data need to be extracted in their own respective columns. The city and state columns are already created. The "location" column has multiple different city and state information separated by a comma (Mountain View, CA). I've tried using:

Replace INTO my_jobs (city)
SELECT substring_index(location, ", ",1) 
FROM my_jobs;

But that adds new rows and doesn't merge the data into the existing fields.

Best Answer

You need to update your table not insert

update my_jobs set city=substring_index(location, ", ",1) ,
state=SUBSTRING(location, LENGTH(location)-2, LENGTH(location))

If you want more explanation add a comment

Related Question