通过对事务进行分组来更新列

Question

我在对象集合col_a中有以下数据：

00004719~BBK~US
00004719~SBK~US
00004719~OBK~GB
00004719~IBK~DE
00004720~BBK~US
00004720~SBK~GB
00004725~IBK~IN

Col_a在数据库中定义为：

create OR REPLACE TYPE col_a AS TABLE OF varchar2(100)

我需要从上述数据中更新 4 upd_tbl列 (col_bbk，col_sbk，col_ibk，col_obk(，以便：如果col_a包含 BBK，则使用事务的 ~ 分隔符的第 3 个值更新col_bbk;如果col_a包含 SBK，则以类似的方式更新事务的col_sbk。事务编号由 col_a 中第一次出现的 ~ 子字符串标识。

transaction_number在upd_tbl中为 pk，upd_tbl和集合数据之间存在一对多。但是，该集合transaction_number作为其元素的一部分。

transaction_number = regexp_substr(col_a, '[^~]+', 1, 1)

所需输出：对于事务"00004719"，col_bbk = 美国，col_SBK = 美国，col_obk = GB，col_ibk = DE。

我目前正在循环中逐行更新单个col_a值来处理它。本质上，对于同一事务"00004719"，更新会根据代码(SBK 等(触发 4 次。

是否可以在一次

中为每个事务编写一次此更新？

下面的不断出错，并显示"sql 命令未正确结束"。

UPDATE upd_tbl
    SET ctry_bbk = (CASE 
                 WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'BBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                 ELSE NULL
                END),
    ctry_sbk = CASE 
                  WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'SBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                  ELSE NULL
                 END,
    ctry_ibk = CASE 
                  WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'IBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                  ELSE NULL
                 END,
    ctry_obk = (CASE 
                  WHEN regexp_substr(tam.column_value, '[^~]+', 1, 2) = 'OBK' THEN regexp_substr(tam.column_value, '[^~]+', 1, 3)
                  ELSE NULL
                 END)
    from (select column_value from table(col_a('00004719~BBK~US','00004719~SBK~US','00004719~IBK~GB','00004719~OBK~IN','00004720~BBK~US','00004720~SBK~RU','00004725~BBK~US'))) tam
    where upd_tbl.transaction_number = regexp_substr(tam.column_value, '[^~]+', 1, 1);

Answer 1

这是一个透视方法：

 select 
  transaction, 
  "'BBK'",
  "'SBK'",
  "'OBK'",
  "'IBK'"
from (
  select 
  regexp_substr("col_a", '[^~]+', 1, 1) as transaction,
  regexp_substr("col_a", '[^~]+', 1, 2) as code,
  regexp_substr("col_a", '[^~]+', 1, 3) as country
  from Table1 t)
pivot 
( 
  MAX(country) for code in ('BBK','SBK','OBK','IBK')
);

这是我正在处理的小提琴。

特别感谢@Lawrence和@Bulat帮助完成这个想法，我只需要聚合MAX而不是COUNT枢轴。

要使用透视创建多列更新，它如下所示：

我已经让它合并和更新同一个表，但将其设置为不同的表就像调整第一个合并/更新语句一样简单。

MERGE INTO Table1 t1
USING 
(   
  select 
    "transactionid", 
    "'BBK'",
    "'SBK'",
    "'OBK'",
    "'IBK'"
  from (
    select 
    regexp_substr("col_a", '[^~]+', 1, 1) as "transactionid",
    regexp_substr("col_a", '[^~]+', 1, 2) as code,
    regexp_substr("col_a", '[^~]+', 1, 3) as country
    from Table1 t)
  pivot 
  ( 
    MAX(country) for code in ('BBK','SBK','OBK','IBK')
  )
) ta ON (regexp_substr(t1."col_a", '[^~]+', 1, 1) = ta."transactionid" )
WHEN MATCHED THEN UPDATE 
    SET "col_bbk" = ta."'BBK'",
     "col_sbk" = ta."'SBK'",
     "col_obk" = ta."'OBK'",
     "col_ibk" = ta."'IBK'",
     "transactionid" = ta."transactionid";

这是这个合并更新枢轴在工作中的一个摆弄。