insert to null value RRS feed

  • Question

  • i have three tables (Families) , (Children) and (Children_Payments) i want to insert payment to each child that his father has state A or B .. the problem is not every father has children and sql server told me "you cannot insert to null children_id value"

    what is the solution .. my sql statment to insert payment is :

    insert into ChildrenPayments

    (payment_value , payment_class , children_id) select @payment_value , @payment_class , fc.children_id

    from (select f.family_id , c.children_id FROM

    (SELECT family_id, father_state FROM dbo.Families WHERE

    (father_state = 'A') or father_state = 'B'))f

    left outer join dbo.Children AS c ON

    c.family_id = f.family_id AND c.children_state <> 'dead' AND c.children_state <> 'absence')fc

    Friday, December 20, 2013 1:13 AM


  • This problem cannot be addressed in the query but can be fixed with a change to the children_id column to allow NULL values.  For example, assuming children_id is an integer data type, you can change the column to allow NULL values:

    ALTER TABLE dbo.ChildrenPayments
    	ALTER COLUMN children_id int NULL;

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Friday, December 20, 2013 3:58 AM
    • Marked as answer by Sofiya Li Thursday, December 26, 2013 5:36 AM
    Friday, December 20, 2013 3:04 AM