我有一张交易记录表
sqlite
我给它加了一个新的专栏。我想要的是根据相同行的记录填充新列。例如
productId brc newColumn
10032 10032-100101 32-A
10231 10231-100102 231-B
我已成功获得newColumn的所需输出:
//The below statement gives me the newColumn result
select cast(substr(productId, 2, 5) as integer) ||
case
when cast(substr(brc, 37, 1) as integer) == 0 then ''
when cast(substr(brc, 37, 1) as integer) == 1 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 2 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 3 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 4 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 5 then '-Î'
END as newColumn
from TRANSACTIONS
现在,我要更新表!我试过:
update TRANSACTIONS
set newColumn = (
select cast(substr(productId, 2, 5) as integer) ||
case
when cast(substr(brc, 37, 1) as integer) == 0 then ''
when cast(substr(brc, 37, 1) as integer) == 1 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 2 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 3 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 4 then '-Î'
when cast(substr(brc, 37, 1) as integer) == 5 then '-Î'
END from TRANSACTIONS
)
where brc = brc;