基于SQLITE中其他列的记录更新列

我有一张交易记录表 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;