| Author |
Topic |
|
agarwasa2008
Posting Yak Master
109 Posts |
 Posted - 2009-11-25 : 21:00:12
|
ProductTable: -- alias bprd_idord_id(int null)quantity(int null)QuantityTable: -- alias aProductIDorderID(int null)Qty(int null)SQL Queryinsert ProductTable (b.quantity)select a.Qtyfrom QuantityTable ainner join ProductTable b on b.ord_id = a. orderIDwhere a.orderID is not nullBasically I need to check the orderId in both table and based on that enter the quantity(whether it is null or not) from QuantityTable to ProductTable. Please note that orderId can be null and quantity can be null. But still the ProductTable needs to be updatedError MessageMsg 515, Level 16, State 2, Line 1Cannot insert the value NULL into column 'prd_id', table 'dbo. ProductTable '; column does not allow nulls. INSERT fails.SA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GeoffreyP
Starting Member
9 Posts |
Posted - 2009-11-26 : 00:55:53
|
| Should you not be using update as to insert you need to insert all fields in the row. |
 |
|
|
kbhere
Yak Posting Veteran
58 Posts |
Posted - 2009-11-26 : 01:05:03
|
| Check whether prd_id set to the NOT NULL in your product table?Msg 515, Level 16, State 2is the error message displayed for inserting a null value to a column which is set to NOT NULL constraint.. Kindly refer..http://www.sommarskog.se/error-handling-I.htmlBalaji.K |
|
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-11-30 : 13:14:40
|
| The issue is as addressed by tkizer - that prd_id is set to identity. What can I chnage in my query to be able to insert.SA |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-30 : 13:18:00
|
quote:
Originally posted by agarwasa2008
The issue is as addressed by tkizer - that prd_id is set to identity. What can I chnage in my query to be able to insert.SA
if its an identity field it should auto generate values. else you need to insert an explicit value for prd_id |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-11-30 : 13:34:10
|
| CREATE TABLE [dbo].[ProductTable]( [prd_id] [int] IDENTITY(1,1) NOT NULL, [emp_id] [char] (8) NOT NULL, [ord_id] [int] NULL, [quantity] [int] NULL,CONSTRAINT [PK_ ProductTable_1] PRIMARY KEY CLUSTERED ( [prd_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]SA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-11-30 : 13:45:51
|
| Please note that I have added [emp_id] [char] (8) NOT NULL, to the DDL I posted earlier. And this did not work. Got the following error message: Msg 515, Level 16, State 2, Line 1Cannot insert the value NULL into column 'emp_id', table 'dbo. ProductTable'; column does not allow nulls. INSERT fails.The statement has been terminated.SA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-11-30 : 13:56:33
|
| Thanks tkizer. It worked. But what I need to do is just update that 1 column - quantity. I just added that column and I need to update the values in that column only. The rest of the table values - epm_id is correctly populated in the ProductTable.SA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-11-30 : 14:08:43
|
| Vow!! I completely missed that. Thanks!! How do I update with a select statement. Please help!!update ProductTable (b.quantity)select a.Qtyfrom QuantityTable ainner join ProductTable b on b.ord_id = a. orderIDwhere a.orderID is not nullSA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
agarwasa2008
Posting Yak Master
109 Posts |
Posted - 2009-11-30 : 14:15:19
|
| Appreciate it so very much .... it worked like a charm.SA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
insert query fails!!