View previous topic :: View next topic
|
Author |
Message |
the_gautam
Active User
Joined: 05 Jun 2005 Posts: 165 Location: Bangalore
|
|
|
|
Can anyone help me for UPDATEing a table by setting a particular field as NULL.
say i have a table MYTABLE in which a nullable column MYCOL1 is there. the data is in the table. now i want to UPDATE the table and place NULL values in the MYCOL1.
using cobol program we may do it by passing -1 to the null-indicators but, what about a QUERY? |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
the_gautam wrote: |
Can anyone help me for UPDATEing a table by setting a particular field as NULL.
say i have a table MYTABLE in which a nullable column MYCOL1 is there. the data is in the table. now i want to UPDATE the table and place NULL values in the MYCOL1.
using cobol program we may do it by passing -1 to the null-indicators but, what about a QUERY? |
update mytable set mycol1 = null;
Of course it may be wise to include a where clause. |
|
Back to top |
|
|
byearun
New User
Joined: 25 Jul 2005 Posts: 17
|
|
|
|
I think it will not work. We tried the same in SPUFI. But it failed with SQLCODE -407 |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
byearun wrote: |
I think it will not work. We tried the same in SPUFI. But it failed with SQLCODE -407 |
Did you even bother to lookup -407 sqlcode?
You can only set a nullable column to null! |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
But it failed with SQLCODE -407 |
You would get this any way you tried, i believe. . . The column is not nullable. . .
It is usually a good idea to look up the error code . . . |
|
Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007 Posts: 792 Location: Chennai, India
|
|
|
|
Here, updating column shouldn't be defiened as NOT NULL... |
|
Back to top |
|
|
byearun
New User
Joined: 25 Jul 2005 Posts: 17
|
|
|
|
Yes, I was trying to update the column which is defined as NOT NULL. How this can done thru SPUFI. |
|
Back to top |
|
|
byearun
New User
Joined: 25 Jul 2005 Posts: 17
|
|
|
|
To more specific, i want to update a null value in the column which has defined as NOT NULL |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
To more specific, i want to update a null value in the column which has defined as NOT NULL |
This cannot be done.
Someone went to some length to define the column as not nullable. Why would you believe it was acceptable for you to force the column to null?
It is probably time for you and your peers to talk with your manager and/or the dba(s) and decide which way the definition should be. |
|
Back to top |
|
|
|