sql修复数据中含有分号结尾的数据

项目开发遇到数据存写错误，导致数据中字段内容，数字中含有分号结尾。如下图：

现在要去去掉末尾的分号，查询sql如下：

select substr(t.BUY_REASON, 1, length(t.BUY_REASON) - 1)
from TM_POTENTIAL_CUSTOMER t
where 1 = 1
  and substr(t.BUY_REASON, length(t.BUY_REASON), 1) = ';'
  and t.BUY_REASON is not null
  and trim(t.BUY_REASON) <> '';

更新sql如下：

update TM_POTENTIAL_CUSTOMER m
set m.BUY_REASON=(select substr(t.BUY_REASON, 1, length(t.BUY_REASON) - 1)
                  from TM_POTENTIAL_CUSTOMER t
                  where 1 = 1
                    and substr(t.BUY_REASON, length(t.BUY_REASON), 1) = ';'
                    and t.BUY_REASON is not null
                    and trim(t.BUY_REASON) <> ''
                    and t.CUSTOMER_NO = m.CUSTOMER_NO
                    and t.ENTITY_CODE = m.ENTITY_CODE
)
where substr(m.BUY_REASON, length(m.BUY_REASON), 1) = ';'
  and m.BUY_REASON is not null
  and trim(m.BUY_REASON) <> ''
;

效果如下图：

注意：update语句一定要加上where条件，过滤需要更新的语句，不然容易把字段更新成null，我在开发测试中就遇到了这个问题，没有判断需要更新的条件，比如一定是最后分号结尾的字段才更新，如果没有这个条件，sql执行多次就会把字段更新成null

错误sql如下：

update TM_POTENTIAL_CUSTOMER m
set m.BUY_REASON=(select substr(t.BUY_REASON, 1, length(t.BUY_REASON) - 1)
                  from TM_POTENTIAL_CUSTOMER t
                  where 1 = 1
                    and substr(t.BUY_REASON, length(t.BUY_REASON), 1) = ';'
                    and t.BUY_REASON is not null
                    and trim(t.BUY_REASON) <> ''
                    and t.CUSTOMER_NO = m.CUSTOMER_NO
                    and t.ENTITY_CODE = m.ENTITY_CODE
)
where
      --substr(m.BUY_REASON, length(m.BUY_REASON), 1) = ';'
   m.BUY_REASON is not null
  and trim(m.BUY_REASON) <> ''
;