Geek asks and answers

Paste and select a combination does not work

This is my query that I want to insert a value that should be selected from another table:

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

... but this gives me an error:

Msg 515, Level 16, State 2, Row 1 Cannot insert NULL value in column "customer_id", table 'Kohinoor_rdfd.kohin_plan.payment_details_kohin'; column Do not allow null. INSERT fails. Application completed.

When trying to execute the following query:

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

... this gives me the following error

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 ')'.
+3
source share
3 answers

Your problem is that you have a non-NULL client id. You also need to insert it into the table:

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';

However, when I see this inserts, sometimes an update is really required:

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';
+2

Values ​​,

INSERT INTO payment_details_kohin(installment_no)
SELECT ISNULL(COUNT(installment_amount), 0) + 2  
FROM kohin_plan.payment_details_insert 
WHERE customer_id = 'KBP100058'
+2

Dear Friend, when you insert a value into one table from another table or by using the selection to insert, then you do not need to make a space in the keyword "Value"

so easy to do the following

INSERT INTO payment_details_kohin(installment_no)
SELECT count(installment_amount) + 2  
FROM kohin_plan.payment_details_insert 
WHERE customer_id = 'KBP100058'
+1
source

More articles:


All Articles