Sql data fragmentation-CodePudding

Brand Model

Audi R8 NULL Audi RS2 NULL

My Dream

Brand Model

Audi R8 Audi RS2

Hello, the model data in this table (A8, RS2,RS4..)how can I separate it from the brand and write it in the model column?

CodePudding user response：

If the brand is separated by space with the model and contains no other space you can use substring_index.

Test it first,

select brand,
       SUBSTRING_INDEX(brand,' ',-1) as model ,
       SUBSTRING_INDEX(brand,' ', 1) as new_brand 
from my_table;

To update the table,

update my_table
set model = SUBSTRING_INDEX(brand,' ',-1) , 
    brand= SUBSTRING_INDEX(brand,' ', 1)

https://dbfiddle.uk/B7hRHld-

Edit

To force the query to use only the rows containing one space you could use

update my_table
set model = SUBSTRING_INDEX(brand,' ',-1) , 
    brand= SUBSTRING_INDEX(brand,' ', 1)
where LENGTH(brand)-LENGTH(REPLACE(brand, ' ', '')) =1

https://dbfiddle.uk/wtZVAUTk

Note, the value ('Audi Rsq8 ') isn't splited because it contains two spaces