Sql-server – always error “cannot insert value NULL” sqlserver

nullsql-server-2008update

I want to update table "tmpiscunitmap.tr_kpi_id" year_period=2016
with value from table "tmp_kpi.tr_kpi_id where year_period=2016"
with condition tmpiscunitmap.tr_kpi_id = tmp_kpi.tr_kpi_id on year_period=2015?

this my query and i get error "cannot insert value NULL into tmpiscunitmap.tr_kpi_id":

UPDATE tmpiscunitmap set tmpiscunitmap.tr_kpi_id=(
SELECT TOP 1
        b.tr_kpi_id
        FROM
        tmptr_kpi
        AS a
    INNER JOIN tmptr_kpi AS b ON a.tr_kpi_code = b.tr_kpi_code
    where
    a.year_period = 2015 AND
    b.year_period =2016 AND
    a.tr_kpi_id=tmpiscunitmap.tr_kpi_id)

Table tmptr_kpi:
tmptr_kpi

Table tmpiscunitmap:
tmpiscunitmap

Best Answer

  • First observation is that your update statement has no WHERE clause. This means that it will update all rows of the table. Are you sure you want that?

    From the comments, it seems that all rows have year_period = 2016 and you do want to update all of them. In my opinion, it wouldn't hurt to explicitly add this condition in the statement. The opposite, it would avoid any unwanted changes, in case you run it in the future, say next year when you have rows with 2016 and 2017.

  • Second, the error is due to for some rows, either the subquery returns a NULL value or it return no rows at all.

    You can easily find the "missing" values (those that the subquery returns no rows) with this:

    SELECT DISTINCT tr_kpi_id
FROM tmpiscunitmap AS t
WHERE NOT EXISTS
      ( SELECT *
        FROM    tmptr_kpi AS a
            INNER JOIN tmptr_kpi AS b ON a.tr_kpi_code = b.tr_kpi_code
        WHERE
            a.year_period = 2015 AND
            b.year_period = 2016 AND
            a.tr_kpi_id = t.tr_kpi_id
      ) 
--  AND year_period = 2016 
 ;
Related Question