将一个列拆分为2列mysql表

Question

我在mysql表中有这个列:

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

如何分割到这个表[成功地做到了，但DK如何改变表本身):

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

任何帮助都是感激的，谢谢!

我的代码只选择2列，但不改变表，我不知道如何在创建和更改列的条件:

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;

我尝试这个更新，但突然区域属性列值消失

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))

Answer 1

lot_location在zone_attribute之前更新-即zone_attribute测试在lot_location中没有发现z。顺便说一句，您的select查询不会产生您声称的结果

颠倒set语句

的顺序

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))
;