3

I have a table like this:

CREATE TABLE `mytable` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `column1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `column2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And some data:

id | column1                                       | column2
____________________________________________________________
1  | Something                                     |
2  | Something else - Lets add more data           |
3  | Something else - Another example - More stuff |

I would like to update this table, by putting everything after the first - (space + dash + space) into column2 and then removing it from column 1. If there is no -, leave it as it is. Such as this:

id | column1        | column2
__________________________________________________
1  | Something      |
2  | Something else | Lets add more data
3  | Something else | Another example - More stuff

Any suggestions?

MagisterMundus
  • 185
  • 1
  • 5

2 Answers2

1
UPDATE mytable
SET column2 = SUBSTRING(column1, 3 + LENGTH(SUBSTRING_INDEX(column1, ' - ', 1))),
    column1 = SUBSTRING_INDEX(column1, ' - ', 1)
Akina
  • 20,750
  • 2
  • 20
  • 22
0
SELECT column1, LEFT(column1, Position(' - ' in column1)) as newCol1,  
substring(column1, Locate(" - ",column1)+3) as newCol2 
FROM mytable where column1 like '% - %';

This should get you in the right direction. If it gives you your desired results you can use it to create your Update statement.

Rio
  • 3
  • 3