批量修改数据库中数据

时间:2021-12-24 07:27:27

批量修改数据库中数据(对数据库中某字段的值增加一个逗号,如果改值不存在逗号时)


update net_shop n
   set n.payment_id  = (select (case
                                 when substr(n1.payment_id,
                                             length(n1.payment_id),
                                             1) = ',' then
                                  n1.payment_id
                                 else
                                  n1.payment_id || ','
                               end) payment_id
                          from net_shop n1
                         where n1.payment_id is not null
                           and n1.id = n.id
                           ),
       n.carriage_id = (select (case
                                 when substr(n1.carriage_id,
                                             length(n1.carriage_id),
                                             1) = ',' then
                                  n1.carriage_id
                                 else
                                  n1.carriage_id || ','
                               end) payment_id
                          from net_shop n1
                         where n1.carriage_id is not null
                           and n1.id = n.id
                           );