CODEWITHSUNDEEP
sqlsql-server-2005
Elaine K
Elaine K

Reputation: 517

cannot insert value NULL into column error shows wrong column name

I've added a new column(NewValue) to my table which holds an int and allows nulls. Now I want to update the column but my insert statement only attempts to update the first column in the table not the one I specified.

I basically start with a temp table that I put my initial data into and it has two columns like this: 

create table #tempTable
(
    OldValue int,
    NewValue int
)

I then do an insert into that table and based on the information NewValue can be null.

Example data in #tempTable: 

  OldValue     NewValue
  --------     --------
  34556        8765432
  34557        7654321
  34558        null

Once that's complete I planned to insert NewValue into the primary table like so: 

insert into myPrimaryTable(NewValue)
select tt.NewValue from #tempTable tt
left join myPrimaryTable mpt on mpt.Id = tt.OldValue
where tt.NewValue is not null

I only want the NewValue to insert into rows in myPrimaryTable where the Id matches the OldValue. However when I try to execute this code I get the following error:

Cannot insert the value NULL into column 'myCode', table 'myPrimaryTable'; column does not allow nulls. INSERT fails.

But I'm not trying to insert into 'myCode', I specified 'NewValue' as the column but it doesn't seem to see it. I've checked NewValue and it is set to allow int and is set to allow null and it does exist on the right table in the right database. The column 'myCode' is actually the second column in the table. Could someone please point me in the right direction with this error?

Thanks in advance.

Upvotes: 0

Views: 2290

Answers (1)

ErikE
ErikE

Reputation: 50251

INSERT always creates new rows, it never modifies existing rows. If you skip specifying a value for a column in an INSERT and that column has no DEFAULT bound to it and is not identity, that column will be NULL in the new row--thus your error. I believe you might be looking for an UPDATE instead of an INSERT.

Here's a potential query that might work for you:

UPDATE mpt
SET
   mpt.NewValue = tt.NewValue
FROM
   myPrimaryTable mpt
   INNER JOIN #tempTable tt
      ON mpt.Id = tt.OldValue -- really?
WHERE
   tt.NewValue IS NOT NULL;

Note that I changed it to an INNER JOIN. A LEFT JOIN is clearly incorrect since you are filtering #tempTable for only rows with values, and don't want to update mpt where there is no match to tt--so LEFT JOIN expresses the wrong logical join type.

I put "really?" as a comment on the ON clause since I was wondering if OldValue is really an Id. It probably is--you know your table best. It just raised a mild red flag in my mind to see an Id column being compared to a column that does not have Id in its name (so if it is correct, I would suggest OldId as a better column choice than OldValue).

Also, I recommend that you never name a column just Id again--column names should be the same in every table in the database. Also, when it comes join time you will be more likely to make mistakes when your columns from different tables can coincide. It is much better to follow the format of SomethingId in the Something table, instead of just Id. Correspondingly, the suggested old column name would be OldSomethingId.

Upvotes: 2

Related Questions