SQL - Insert into one column within table

Question

so I just added a new column to a table and now i want to populate that column in the pre-existing rows. This my current query, but it keeps erroring, because its trying to insert into the first column of the table.

insert into Product_Database(UPC)
select u.UPC
from upc_temp u
    join upc_temp_gers g on u.PartNumber = g.PartNumber
    join Product_Database p on p.ITM_CD = g.ItemCode
where u.UPC is not null

and this is the error its returning

Cannot insert the value NULL into column 'ITM_CD'

Clearly i am not trying to insert into the 'ITM_CD' column. Just 'UPC'.

Any help would be greatly appreciated. Thanks!

Have you looked at the table definition of Product_Database? I'm certain there is a not null constraint on the column ITM_CD and you are not providing any value for ITM_CD. Hence the row would have NULL as a value for ITM_CD, which the constraint prevents. — Corion
– Corion, Commented Oct 18, 2018 at 19:50

Tab Alleman · Accepted Answer · 2018-10-18 19:51:06Z

3

now i want to populate that column in the pre-existing rows.

After you add a new column to a table, you don't populate the new column for existing rows with an INSERT.

You do it with an UPDATE.

answered Oct 18, 2018 at 19:51

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2018-10-18 20:19:23Z

0

Presumably the logic you want is:

update p 
    set UPC = u.UPC
    from upc_temp u join
         upc_temp_gers g
         on u.PartNumber = g.PartNumber join
         Product_Database p
         on p.ITM_CD = g.ItemCode
    where u.UPC is not null;

answered Oct 18, 2018 at 20:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Ruchira · Accepted Answer · 2018-10-20 03:03:32Z

0

You have to use UPDATE query for this.

answered Oct 20, 2018 at 3:03

Ruchira

1451 gold badge2 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL - Insert into one column within table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related