Multi column updates

Hi All, So far, I have been using following update to update all "int" columns and it was working okay. Yesterday, I changed the query to add a "char" column. For some reason, I got an error saying "cannot insert null value into column <charColumn>". Please note, there was a value from the fetched table. Also, to re-confirm, I replaced the column with another "char" column name and got the same error. Is this a limitation of multi column update? or I am missing a valid syntax here... At this point, I am asking this question as a common question for IDS versions that allow multi column updates and therefore not stating any specific version. If you still need it, please let me know. UPDATE tab1 SET (int_col_1, int_col_2, char_col_1) = (( select int_1, int_2, char_1 from tab2 where ......)) WHERE .....; As always, thanks in advance... Regards, Dharmendra

Sounds like you have some 'NOT NULL' constraints on your char columns. Check your table layout in dbaccess -> columns and see if Nulls for these are set to NO. Keith On 1 October 2010 15:29, DHARMENDRA SHARMA <dharmendrasharma@hotmail.com> wrote: > Hi All, > > So far, I have been using following update to update all "int" columns and it > was working okay. Yesterday, I changed the query to add a "char" column. For > some reason, I got an error saying "cannot insert null value into column > <charColumn>". Please note, there was a value from the fetched table. Also, to > re-confirm, I replaced the column with another "char" column name and got the > same error. Is this a limitation of multi column update? or I am missing a > valid syntax here... > > At this point, I am asking this question as a common question for IDS versions > that allow multi column updates and therefore not stating any specific > version. If you still need it, please let me know. > > UPDATE tab1 > > SET (int_col_1, int_col_2, char_col_1) = > > (( select int_1, int_2, char_1 from tab2 where ......)) > WHERE .....; > > As always, thanks in advance... > > Regards, > > Dharmendra > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Hi Keith, Thanks! Yes, it does. I should have mentioned that in my first email.... I understand if the value from char_1 of tab_2 table is null and causes this error. But in this case, the char_1 column also has "not null" contraints and therefore, it will always have a value. In this case, how this error is possible? Regards, Dharmendra > To: ids@iiug.org > From: smiley73@gmail.com > Subject: Re: Multi column updates [21500] > Date: Fri, 1 Oct 2010 10:58:54 -0400 > > Sounds like you have some 'NOT NULL' constraints on your char columns. Check > your table layout in dbaccess -> columns and see if Nulls for these > are set to NO. > > Keith > > On 1 October 2010 15:29, DHARMENDRA SHARMA <dharmendrasharma@hotmail.com> > wrote: > > Hi All, > > > > So far, I have been using following update to update all "int" columns and > it > > was working okay. Yesterday, I changed the query to add a "char" column. For > > some reason, I got an error saying "cannot insert null value into column > > <charColumn>". Please note, there was a value from the fetched table. Also, > to > > re-confirm, I replaced the column with another "char" column name and got > the > > same error. Is this a limitation of multi column update? or I am missing a > > valid syntax here... > > > > At this point, I am asking this question as a common question for IDS > versions > > that allow multi column updates and therefore not stating any specific > > version. If you still need it, please let me know. > > > > UPDATE tab1 > > > > SET (int_col_1, int_col_2, char_col_1) = > > > > (( select int_1, int_2, char_1 from tab2 where ......)) > > WHERE .....; > > > > As always, thanks in advance... > > > > Regards, > > > > Dharmendra > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

When the sub Select does not return any thing? That is exactly the reason. your tab1 does not allow a Null to be inserted into the column. Frank On Fri, Oct 1, 2010 at 11:22 AM, dharmendra sharma < dharmendrasharma@hotmail.com> wrote: > Hi Keith, > > Thanks! > > Yes, it does. I should have mentioned that in my first email.... > > I understand if the value from char_1 of tab_2 table is null and causes > this > error. But in this case, > the char_1 column also has "not null" contraints and therefore, it will > always > have a value. In this case, > how this error is possible? > > Regards, > > Dharmendra > > > To: ids@iiug.org > > From: smiley73@gmail.com > > Subject: Re: Multi column updates [21500] > > Date: Fri, 1 Oct 2010 10:58:54 -0400 > > > > Sounds like you have some 'NOT NULL' constraints on your char columns. > Check > > your table layout in dbaccess -> columns and see if Nulls for these > > are set to NO. > > > > Keith > > > > On 1 October 2010 15:29, DHARMENDRA SHARMA <dharmendrasharma@hotmail.com > > > > wrote: > > > Hi All, > > > > > > So far, I have been using following update to update all "int" columns > and > > it > > > was working okay. Yesterday, I changed the query to add a "char" > column. > For > > > some reason, I got an error saying "cannot insert null value into > column > > > <charColumn>". Please note, there was a value from the fetched table. > Also, > > to > > > re-confirm, I replaced the column with another "char" column name and > got > > the > > > same error. Is this a limitation of multi column update? or I am > missing a > > > valid syntax here... > > > > > > At this point, I am asking this question as a common question for IDS > > versions > > > that allow multi column updates and therefore not stating any specific > > > version. If you still need it, please let me know. > > > > > > UPDATE tab1 > > > > > > SET (int_col_1, int_col_2, char_col_1) = > > > > > > (( select int_1, int_2, char_1 from tab2 where ......)) > > > WHERE .....; > > > > > > As always, thanks in advance... > > > > > > Regards, > > > > > > Dharmendra > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001485f1ed4cfe5c9a0491909f60

Hi Frank and Keith, Thanks a lot to both of you for helping me out on this. Frank was correct. It was related to a subquery not returning any row.. Here is what I did to resolve it... UPDATE tab1 SET (int_col_1, int_col_2, char_col_1) = (( select int_1, int_2, char_1 from tab2 where ......)) WHERE int_col3 in (select int_3 from tab2); <-- this is what I added.. I added another "IN" clause in WHERE clause of the main table to consider only those records where int_col3 exists in tab2....(I don't know how I missed it in first place and why I didn't catch it before....?) Once again thanks a lot to both of you (and to those who were about to send reply to my query).. Regards, Dharmendra > To: ids@iiug.org > From: yunyaoqu@gmail.com > Subject: Re: Multi column updates [21502] > Date: Fri, 1 Oct 2010 12:24:17 -0400 > > When the sub Select does not return any thing? > > That is exactly the reason. your tab1 does not allow a Null to be inserted > into the column. > Frank > > On Fri, Oct 1, 2010 at 11:22 AM, dharmendra sharma < > dharmendrasharma@hotmail.com> wrote: > > > Hi Keith, > > > > Thanks! > > > > Yes, it does. I should have mentioned that in my first email.... > > > > I understand if the value from char_1 of tab_2 table is null and causes > > this > > error. But in this case, > > the char_1 column also has "not null" contraints and therefore, it will > > always > > have a value. In this case, > > how this error is possible? > > > > Regards, > > > > Dharmendra > > > > > To: ids@iiug.org > > > From: smiley73@gmail.com > > > Subject: Re: Multi column updates [21500] > > > Date: Fri, 1 Oct 2010 10:58:54 -0400 > > > > > > Sounds like you have some 'NOT NULL' constraints on your char columns. > > Check > > > your table layout in dbaccess -> columns and see if Nulls for these > > > are set to NO. > > > > > > Keith > > > > > > On 1 October 2010 15:29, DHARMENDRA SHARMA <dharmendrasharma@hotmail.com > > > > > > wrote: > > > > Hi All, > > > > > > > > So far, I have been using following update to update all "int" columns > > and > > > it > > > > was working okay. Yesterday, I changed the query to add a "char" > > column. > > For > > > > some reason, I got an error saying "cannot insert null value into > > column > > > > <charColumn>". Please note, there was a value from the fetched table. > > Also, > > > to > > > > re-confirm, I replaced the column with another "char" column name and > > got > > > the > > > > same error. Is this a limitation of multi column update? or I am > > missing a > > > > valid syntax here... > > > > > > > > At this point, I am asking this question as a common question for IDS > > > versions > > > > that allow multi column updates and therefore not stating any specific > > > > version. If you still need it, please let me know. > > > > > > > > UPDATE tab1 > > > > > > > > SET (int_col_1, int_col_2, char_col_1) = > > > > > > > > (( select int_1, int_2, char_1 from tab2 where ......)) > > > > WHERE .....; > > > > > > > > As always, thanks in advance... > > > > > > > > Regards, > > > > > > > > Dharmendra > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001485f1ed4cfe5c9a0491909f60 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >