MYSQL: Merge data from one column to another

Question

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.

Answer 1

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

• axxic3: Thanks but I forgot to mention there are multiple different values in the location column, that would replace only one row correct?
• Krismorte: Sorry, I made a test first and forgotten to change the code :D
• axxic3: How would I select the cities if they are varying lengths?
• Krismorte: What your mean select? I use SUBSTRING, LENGTH and substring_index funcitons to find the size, position and extract the text w3resource.com/mysql/string-functions/mysql-length-function.php techonthenet.com/mysql/functions/substring.php w3schools.com/sql/func_mysql_substring_index.asp
• axxic3: I edited your script to just substring index and that worked for the cities. The issue I was having is that the cities dont have a fixed position like a two character state abbreviation would. I set the substring index to use "comma space" as a delimiter and I didn't have to set a character length. Thanks for your help!