数据库补0

首先备份原表：

create table sop_tepl_prmt_ref_0508 as SELECT * from sop_tepl_prmt_ref;

 

再在备份表里操作：

UPDATE shpsit.sop_tepl_prmt_ref_0508

SET PRMT_TMPL_CODE = (

CASE

WHEN length(PRMT_TMPL_CODE) = '1' THEN

concat('000000', PRMT_TMPL_CODE)

WHEN length(PRMT_TMPL_CODE) = '2' THEN

concat('00000', PRMT_TMPL_CODE)

WHEN length(PRMT_TMPL_CODE) = '3' THEN

concat('0000', PRMT_TMPL_CODE)

WHEN length(PRMT_TMPL_CODE) = '4' THEN

concat('000', PRMT_TMPL_CODE)

WHEN length(PRMT_TMPL_CODE) = '5' THEN

concat('00', PRMT_TMPL_CODE)

WHEN length(PRMT_TMPL_CODE) = '6' THEN

concat('0', PRMT_TMPL_CODE)

  ELSE 

PRMT_TMPL_CODE

END

),

 PRMT_CODE = (

CASE

WHEN length(PRMT_CODE) = '1' THEN

concat('00000', PRMT_CODE)

WHEN length(PRMT_CODE) = '2' THEN

concat('0000', PRMT_CODE)

WHEN length(PRMT_CODE) = '3' THEN

concat('000', PRMT_CODE)

WHEN length(PRMT_CODE) = '4' THEN

concat('00', PRMT_CODE)

WHEN length(PRMT_CODE) = '5' THEN

concat('0', PRMT_CODE)

ELSE 

PRMT_CODE

END

);