SQL insert into fails using substring from same table

Question

I would like to create process using substring from table1.column2 and insert results into table1.column6. I am trying the query below but my insert fails. It tries to insert into another column (column1) which is the id column. I specify column6 but it does not work.

Insert into table1(column6)
   Select substring(column1,1,3)
   from table1

Error:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Column1ID', table 'dbo.table1'; column does not allow nulls. INSERT fails.

Please check the definition of table1 column6 and check if it allows NULL value. — Paresh J, Nov 13 '14 at 14:06
The error is very clear. Have you researched it before submitting? — Hozikimaru, Nov 13 '14 at 14:07

score 3 · Accepted Answer · answered Nov 13 '14 at 14:19

3

If your table contain data , but column6 is empty (null), you must use update command

update table1 set column6 = substring(column1,1,3)

that use data stored into table1 and update column6

answered Nov 13 '14 at 14:19

Mehdi Haghshenas

2,433
1
16
35

score 1 · Answer 2 · edited May 23 '17 at 10:30

There is a fundamental difference between the insert and update statements - insert creates a brand new row, whereas update updates a row that already exists (even if data in a particular column for a row does not yet exist)

What are differences between INSERT and UPDATE in MySQL? - i realize you're sql server but same principle applies.

It sounds like you may actually be trying to update a column value, not insert a new row with a value in a column.

Given the following table and data:

table1
----
Id int primary key identity(1,1)
column1 varchar(50) not null
column6 varchar(50) null

id    column1    column6
-----
1     some value    null
2     some other value    null

note that if you were to attempt your query:

insert into table1(column6) select substring(column1,1,3) from table1

this would attempt to take "some value" and "some other value" into new rows 3 and 4, with null in column1, and the substring values into column6. Note that this would fail because you're attempting to insert rows into table1 with null values (column1) in a not nullable column.

what you (probably) actually want is something like:

update table1
set column6 = substring(column1,1,3)

which will update the table for (in this case all rows) setting column6s value to the substring values of column1

I see what the error I made is now. I tried the update and it worked. — Zman, Nov 13 '14 at 14:51

score 1 · Answer 3 · answered Nov 13 '14 at 14:43

I think you are trying to update the table. Use ISNULL to avoid null values.

 UPDATE table1 SET column6 = ISNULL(SUBSTRING(column1,1,3), '')

If you insist on insert. Do check all the columns accept null other than Column6. And Query will insert a bulk of data.

Insert into table1(column6)
Select ISNULL(SUBSTRING(column1,1,3), '')
from table1

SQL insert into fails using substring from same table

3 Answers3