Changing data in mysql table

[mpc123]

Hi i have data in a mysql table which is about 70000 rows. In one column there is information that I would like to split into 2 rows. The information I want to send over to the other named row is nicely sat in brackets eg (18) . It is imperative whilst i move these over to the new named row that they stay aligned with the same row, as the information will be all wrong if not.

EG - i need to move the (18) and (24) over to colomn 4 and delete from column 2 the (18) and (24)

Colomn 1 Colomn 2 Colomn 3 Colomn 4

row 1 xxxxxx xxx (18) xxxxxxx

row 2 xxxxxx xxx (18) xxxxxxx

row 3 xxxxxx xxx (24) xxxxxxx

row 4 xxxxxx xxx (18) xxxxxxx

What code would I need to run to enable me to do this.

Thanks for your help

[rch1231]

It is difficult to tell where one column ends and the next one starts from your posting. Next time may I suggest you use the CODE tag to force a non proportional font or do something like this to get a format and definition of the first 10 records we can easily ready and generate code for:

mysqldump -c -u dbuser -puserpassword MyDatabase MyTable -w"ID<10"

However if it is a fixed lenght file and the (18) and (24) are always in positions 4,5,6 and 7 of column2 then it is easy. Parts of strings can be extracted and displayed. For example, LEFT( ), MID( ), and RIGHT( ) extract substrings from the left, middle, or right part of a string.

I would create a query to select the results first, then post them either to a new table and when the new table is correct upload it in place of the old. Or use update to add two new columns (Column4 and Column5) and once I have verified the output write column 5 to column2 and delete 5.

Creating a new table has always worked best for me. That way I can run it over and over till I get the parsing correct and have the original table intact.

First get your select working
Just in case I am assuming there are some characters in Column2 after the (18) and (24) that you might need in the new Colum2. If not all you need is the left( ) and you can drop the concat and right.

SELECT Column1, 
CONCAT(left(column2,3), right(Column2,<number of characters after (18) and (24))),
Column3, 
mid(Column2,4,4) 
from mytable

Then write it to the new table:

Create Table NewTable SELECT Column1, 
CONCAT(left(column2,3), right(Column2,<number of characters after (18) and (24))),
Column3, 
mid(Column2,4,4) 
from mytable

If NewTable looks good relace mytable with it.