update table1 set table1.hp_dn_id =(
select
case LEN(rtrim(h.hp_dn_id))
WHEN 1 THEN '00000'+h.hp_dn_id
WHEN 2 THEN '0000'+h.hp_dn_id
WHEN 3 THEN '000'+h.hp_dn_id
WHEN 4 THEN '00'+h.hp_dn_id
WHEN 5 THEN '0'+h.hp_dn_id
else
h.hp_dn_id
end
from table1 as h
where h.hp_dn_id=table1.hp_dn_id
)
select
case LEN(rtrim(h.hp_dn_id))
WHEN 1 THEN '00000'+h.hp_dn_id
WHEN 2 THEN '0000'+h.hp_dn_id
WHEN 3 THEN '000'+h.hp_dn_id
WHEN 4 THEN '00'+h.hp_dn_id
WHEN 5 THEN '0'+h.hp_dn_id
else
h.hp_dn_id
end
from table1 as h
where h.hp_dn_id=table1.hp_dn_id
)