1

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.

Dumb Question
  • 365
  • 1
  • 3
  • 14
  • Regarding your modification, I would suggest you look into [SPLIT](http://stackoverflow.com/a/2696901/3504007). 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 Jan 02 '17 at 05:30
  • yes your solution is great, I just dont know how to make the hash in the url still in there. – Dumb Question Jan 02 '17 at 05:35
  • 1
    If the above stored procedure is installed, you can do `CONCAT(ign1, '-', ign2, '-', SPLIT(url, '-', 2)) \`url\`` – Kraang Prime Jan 02 '17 at 05:40

1 Answers1

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 :

Community
  • 1
  • 1
Kraang Prime
  • 9,981
  • 10
  • 58
  • 124
  • I think -gg- is not hardcoded but based on the user2_ign column – barudo Jan 02 '17 at 04:22
  • @barudo if that is the case, he can replace `REPLACE(url, '-', '-gg-') \`url\`` with `REPLACE(url, '-', CONCAT('-', user1_ign, '-') \`url\`` – Kraang Prime Jan 02 '17 at 04:24
  • what is the `url` inside of the `REPLACE` ? is that the column name itself? – Dumb Question Jan 02 '17 at 04:27
  • @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. – Kraang Prime Jan 02 '17 at 04:28
  • uhm, I think you missed a close bracket `)` for REPLACE ? where do I need to put it? before `url` ? – Dumb Question Jan 02 '17 at 04:42
  • In the comment, yes, missing a bracket, but in the example solution, no bracket is missing. Will put into solution the alternative – Kraang Prime Jan 02 '17 at 04:43
  • uhm I have a little problem regarding with changing the url, I have this old url `228-HasZNsh` and what I need the url to become is `ign1-ign2-HasZNsh`. `ign1` and `ign2` is a string. I tried to remove the url inside the `REPLACE` but it gives me an error. – Dumb Question Jan 02 '17 at 05:03