Split a column into 2 columns mysql table-CodePudding

I have this column in mysql table:

LOT_LOCATION
SGBAKE.0013
SGHAST.0008Z1
SGHAST.0011ZU

How to split to this table[MANAGED TO DO SO BUT DK HOW TO CHANGE THE TABLE ITSELF):

LOT_LOCATION, Zone Attribute
SGBAKE.0013, ''
SGHAST.0008, Z1
SGHAST.0011, ZU

Any help is appreciated thanks!

my code only select 2 columns but does not alter the table and I dont know how to put condition in creation and alteration of columns:

select if(locate('Z',LOT_LOCATION)=0,LOT_LOCATION,substring_index(LOT_LOCATION, 'Z', 1)), 
       if(locate('Z',LOT_LOCATION)=0,'',substring_index(LOT_LOCATION, 'Z', -1)) 
            As Zone_Attribute 
from skynet_msa.Lab_WIP_History;

I tried this UPDATE but suddenly the zone attribute column values disappear

UPDATE Lab_WIP_History
    SET LOT_LOCATION = if(locate('Z',LOT_LOCATION)=0,LOT_LOCATION,substring_index(LOT_LOCATION, 'Z', 1)), 
    `Zone Attribute` = if(locate('Z',LOT_LOCATION)=0,'',substring_index(LOT_LOCATION, 'Z', -1))

CodePudding user response：

lot_location is updated before zone_attribute - ie the zone_attribute test finds no z in lot_location btw your select query does not produce the result you claim

reverse the order of the set statement

UPDATE Lab_WIP_History
    SET `Zone Attribute` = if(locate('Z',LOT_LOCATION)=0,'',substring_index(LOT_LOCATION, 'Z', -1)), 
     LOT_LOCATION = if(locate('Z',LOT_LOCATION)=0,LOT_LOCATION,substring_index(LOT_LOCATION, 'Z', 1))
;