sql - 插入并选择组合不起作用

Question

这是我的查询，我想插入应该从另一个表中选择的值:

insert into payment_details_kohin(installment_no)
 values(
select count(installment_amount)+2  
from kohin_plan.payment_details_insert 
where customer_id='KBP100058'
)

...但是它给了我一个错误:

Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'customer_id', table 'kohinoor_rdfd.kohin_plan.payment_details_kohin'; column does not allow nulls. INSERT fails. The statement has been terminated.

当我尝试以下查询时:

insert into payment_details_kohin(installment_no)
values(
select count(installment_amount)+2  
from kohin_plan.payment_details_insert 
where customer_id='KBP100058'
)

...它给了我以下错误

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

Answer 1

您的问题是您有一个非 NULL 客户 ID。您还需要将其插入到表中:

insert into payment_details_kohin(customer_id, installment_no)
    select customer_id, count(installment_amount)+2  
    from kohin_plan.payment_details_insert 
    where customer_id='KBP100058';

但是，当我看到这样的插入时，有时真正需要的是更新:

update payment_details_kohin
    set installment_no = (select count(installment_amount) + 2
                          from kohin_plan.payment_details_insert 
                          where payment_details_kohin.customer_id = payment_details_insert.customer_id
                         )
    where customer_id = 'KBP100058';