Postgresql:从select更新总是失败

Question

请帮忙，

我将用account_invoice_line(no_pajak列(中的值更新account_move_line列tax_number(中的数值。但它总是返回Null。

这是我的代码：

update account_move_line aml set tax_number =  (
select no_pajak as tax_number from account_invoice_line 
where invoice_id = aml.invoice_id and no_pajak is not NULL  
)  
where tax_number is NULL and date > '2017-08-01'

非常感谢

Answer 1

我不确定你是否可以用这种方式使用横向连接，所以用CTE:重写它

with u as (
select no_pajak, invoice_id id
from account_invoice_line 
join account_move_line aml on invoice_id = aml.invoice_id and no_pajak is not NULL   
)
update account_move_line aml set tax_number =  no_pajak
from u 
where invoice_id = id
and tax_number is NULL and date > '2017-08-01'

应该有效，尽管似乎过于复杂