How to change the data of a column in mysql?

Question

I need to change some data of one column from table1 and then, I will copy some of the table1 data to new_table, here is my example tables.

table1

id | url | user1_ign | user2_ign | message | fields that are not needed anymore

new_table

id | url | user1_ign | user2_ign | message

Basically, table1 have fields that are not in new_table. My problem is I do not know how to change the data in a field while copying it to a new table (already searched here).

I need to change the data of the url. Here is the layout.

table1

id | url        | user1_ign | user2_ign | message     | some field

1  | jj-HasZNsh | jj        | gg        | hello dude! | ...

new_table

id | url           | user1_ign | user2_ign | message

1  | jj-gg-HasZNsh | jj        | gg        | hello dude!

That is what I needed to do to, as you can see, I need to change the url in new_table based on the user1_ign and user2_ign. Is there a way of how to solve this?

UPDATE

I have this kind of url in table1 number-HasZNsh or alphabet-HasZNsh.

I need them to become like this in new_table

number-HasZNsh -> ign1-ign2-HasZNsh
alphabet-HasZNsh -> ign1-ign2-HasZNsh

This is what I need to do specifically.

Regarding your modification, I would suggest you look into SPLIT. Once the stored procedure is installed on the server, you can use it like any other command as illustrated in that solution. For your situation, it's really just tweaking the REPLACE(url ......) line in my solution to your taste. — Kraang Prime
– Kraang Prime, Commented Jan 2, 2017 at 5:30
yes your solution is great, I just dont know how to make the hash in the url still in there. — Dumb Question
– Dumb Question, Commented Jan 2, 2017 at 5:35
If the above stored procedure is installed, you can do CONCAT(ign1, '-', ign2, '-', SPLIT(url, '-', 2)) `url` — Kraang Prime
– Kraang Prime, Commented Jan 2, 2017 at 5:40

Community · Accepted Answer · 2017-05-23 12:01:54Z

3

You can combine the INSERT statement for your destination table followed SELECT to set the values to be inserted. For your url field as you specify above, you can use REPLACE to replace a string inside a string.

INSERT INTO 
   `new_table` (id, url, user1_ign, user2_ign, message)
SELECT
   id, 
   REPLACE(url, '-', '-gg-') `url`,
   user1_ign,
   message
FROM 
   `table1`

If you wish to grab data from another field for the gg part of the REPLACE line, you would use :

INSERT INTO 
   `new_table` (id, url, user1_ign, user2_ign, message)
SELECT
   id, 
   REPLACE(url, '-', CONCAT('-', user2_ign, '-') `url`,
   user1_ign,
   message
FROM 
   `table1`

For more information on the command syntax as used above :

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jan 2, 2017 at 4:20

Kraang Prime

10.6k11 gold badges66 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

barudo Over a year ago

I think -gg- is not hardcoded but based on the user2_ign column

Kraang Prime Over a year ago

@barudo if that is the case, he can replace REPLACE(url, '-', '-gg-') `url` with REPLACE(url, '-', CONCAT('-', user1_ign, '-') `url`

Dumb Question Over a year ago

what is the url inside of the REPLACE ? is that the column name itself?

Kraang Prime Over a year ago

@DumbQuestion yes, that is the column name itself. Between SELECT and FROM are the columns selected. You can transform data using other SQL commands in there as I did with REPLACE and in the last comment CONCAT. This example also assumes that the field types and sizes in `new_table` are sufficient for the data being copied/transformed.

Dumb Question Over a year ago

uhm, I think you missed a close bracket ) for REPLACE ? where do I need to put it? before url ?

|

Collectives™ on Stack Overflow

How to change the data of a column in mysql?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related