-1

I need to take an address that consists of "city, state" from column "location" and populate 2 new columns "city" and "state" but leave location the way it is, now I have done this with a SUBSTRING_INDEX command but I have to run the command everytime to do this, how can I make it stick?

Here is my substring code:

SELECT  distinct id, first_name, last_name,
SUBSTRING_INDEX(location, ' ,', 1) AS City,
SUBSTRING_INDEX(location, ' ,', -1) AS State,
SUBSTRING_INDEX(seeking,  ' ,', 1) AS Seeking_1,
SUBSTRING_INDEX(seeking,  ' ,', -1) AS Seeking_2,
SUBSTRING_INDEX(interests,' ,', 1) AS Interests_1,
SUBSTRING_INDEX(interests,' ,', -1) AS Interests_2,
SUBSTRING_INDEX(interests,' ,', 1) AS Interests_3
FROM my_contacts
tshepang
  • 12,111
  • 21
  • 91
  • 136
bodhi926
  • 21
  • 7
  • Which language is this? – Zach Stark Nov 01 '13 at 03:40
  • select location, substring_index(location, ' ',1) as city, substring_index(location, ' ',-1) as state from my_contacts................sorry that first was old code – bodhi926 Nov 01 '13 at 03:40
  • Is this tsql, pl-sql, or mysql sql? – James A Mohler Nov 01 '13 at 03:43
  • Looks like the language is SQL. @bodhi926 use the UPDATE statement to make it stick. – groverboy Nov 01 '13 at 03:46
  • apologies, yes MySQL 5.2.38 – bodhi926 Nov 01 '13 at 03:55
  • the above works, but as I said I basicly want to alter the table but it does not seem to like ALTER TABLE, so in looking at my book again it looks like I want something like ALTER TABLE my_contacts MODIFIY location... – bodhi926 Nov 01 '13 at 03:56
  • You should find a SQL tutorial. `SELECT` does exactly what it says it does - it **selects** existing data and returns it to you. If you want to change the data, you need `UPDATE`, and if you want to add data you use `INSERT`. Any beginning SQL tutorial would explain this for you. – Ken White Nov 01 '13 at 04:02
  • so not alter table but update, kinda get those 2 confused still. can you tell me just what the difference between the 2? they both seem to do the same thing – bodhi926 Nov 01 '13 at 04:04
  • I will try reposting the question – bodhi926 Nov 01 '13 at 04:15
  • update my_contacts set city = substring_index(location, ',', 1), state = substring_index(location, ',',-1) ; – bodhi926 Nov 01 '13 at 16:17

1 Answers1

0

Much to my delight rather than trying to move items in a table via code, all one must do is right click on the table and "edit table data". And you can move anything to anywhere because it lets you just type in to the field what you want.

bodhi926
  • 21
  • 7