1

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

  • Does tmpiscunitmap have a year_period column? – ypercubeᵀᴹ Jun 14 '15 at 10:42
  • yes, column year_period value=2016, but value column tmpiscunitmap.tr_kpi_id = value column tmptr_kpi.tr_kpi_id on tmptr_kpi.year_period=2015, i want update value tmpiscunitmap.tr_kpi_id with value tmptr_kpi.year_period=2016 – abdul Jun 14 '15 at 10:46
  • Is tmpiscunitmap (tr_kpi_id) the primary key of the table? – ypercubeᵀᴹ Jun 14 '15 at 10:58
  • If you added a few sample rows from the 2 tables and the CREATE TABLE scripts, it would be much easier for others to answer. – ypercubeᵀᴹ Jun 14 '15 at 11:00
  • tmpiscunitmap (tr_kpi_id) isn't the primary key of the table – abdul Jun 14 '15 at 11:10
1
  • 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 
     ;
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.