stopm
asked on
Oracle. Replace or remove data from one column
Oracle 9i
What sql command can i use to drop or replace the data in one column of a table for one of the entrys in that table.
When the data in this column is split across two segments and if i use something like
update *TABLE* set *COLUMN* = NULL where name = *ROW NAME*
When i look at my application that uses this database it complains about 'nullnull' entry being invalid. This error message does not occur if data does not extend across 2 segments
What i am trying to acheive is to replace everything in the COLUMN field with new data (but not delete the row)
What sql command can i use to drop or replace the data in one column of a table for one of the entrys in that table.
When the data in this column is split across two segments and if i use something like
update *TABLE* set *COLUMN* = NULL where name = *ROW NAME*
When i look at my application that uses this database it complains about 'nullnull' entry being invalid. This error message does not occur if data does not extend across 2 segments
What i am trying to acheive is to replace everything in the COLUMN field with new data (but not delete the row)
update orders set bin = ( select binfrom from zones where orders.BIN = zones.BINTO )
may be this help u or pls explain with example..
may be this help u or pls explain with example..
Can you post the relevant table structure with some sample data and what you expect the end result to look like?
ASKER
To explain a bit better as suggeted by 'awking00' i have attached a sample of 'TABLE1'
The ABC name entry has 3 rows.
Line 4 in spread sheet (Type 7 , Segment 0) has in the DATA column multiple entrys. When this column has more entrys then its max characters a second line/ segement (line 2 in spreadsheet) is created and additional data is placed here. Segment 2 being the extension of segment 1
So what i need to do is update all the values in the DATA column (where name = 'ABC123' and 'TYPE' = 7) on a regular basis via sql (rather then using the application).
The data may only require 1 segment rather then 2 segments. If i leave the 2nd segment blank (NULL) then the application complains.
If i only have 1 segment eg. i delete the segment 2 row but then try and do an 'update table' but i have more data then can fit in segment 1 row i will get error message saying too much data.
How do i get it to extend across 2 or even 3 segments if required or remove them if not required.
Hope this is clearer
Table-example.XLS
The ABC name entry has 3 rows.
Line 4 in spread sheet (Type 7 , Segment 0) has in the DATA column multiple entrys. When this column has more entrys then its max characters a second line/ segement (line 2 in spreadsheet) is created and additional data is placed here. Segment 2 being the extension of segment 1
So what i need to do is update all the values in the DATA column (where name = 'ABC123' and 'TYPE' = 7) on a regular basis via sql (rather then using the application).
The data may only require 1 segment rather then 2 segments. If i leave the 2nd segment blank (NULL) then the application complains.
If i only have 1 segment eg. i delete the segment 2 row but then try and do an 'update table' but i have more data then can fit in segment 1 row i will get error message saying too much data.
How do i get it to extend across 2 or even 3 segments if required or remove them if not required.
Hope this is clearer
Table-example.XLS
ASKER
Another comment.
I would prefer not to have to alter tablespace size for the data column (unless i can do it for just this row of data) as i dont want to impact the way the application currently works.
I would prefer not to have to alter tablespace size for the data column (unless i can do it for just this row of data) as i dont want to impact the way the application currently works.
ASKER
Here is the error message i get if text in DATA column is tool large 'ORA-01401: inserted value too large for column"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi mrjoltcola
Thanks for the reply. I was trying to avoid altering column size if possible as the java application splits the data over segments when it updates this table. So i wanted to replicate this same action when i updated this table directly with my script. eg split the data into 254 bit segements and do multiple updates as required for how ever many segments i need. Is it possible to do this?
If not then i will have to look at increasing colmn size.
Thanks for the reply. I was trying to avoid altering column size if possible as the java application splits the data over segments when it updates this table. So i wanted to replicate this same action when i updated this table directly with my script. eg split the data into 254 bit segements and do multiple updates as required for how ever many segments i need. Is it possible to do this?
If not then i will have to look at increasing colmn size.
The alter is such a minor thing, I am not sure why you want to avoid it.
The error you are getting is simply telling you you tried to append or insert more data into a column than could fit.
Looking at your sample data, I am not sure why the design was chosen to split across multiple records in the first place. I would have used a VARCHAR2(4000) or I would have used a child table to hold the attributes, instead of packing them into a single field.
I note that your sample data has spaces in it, is there a reason for that?
U || VWM || XYZ
Trim the spaces out and you get:
U||VWM||XYZ
which gives you more space. Also, why are you using double bars ||, is it possible to use a single?
Thats all I can suggest. Good luck.
The error you are getting is simply telling you you tried to append or insert more data into a column than could fit.
Looking at your sample data, I am not sure why the design was chosen to split across multiple records in the first place. I would have used a VARCHAR2(4000) or I would have used a child table to hold the attributes, instead of packing them into a single field.
I note that your sample data has spaces in it, is there a reason for that?
U || VWM || XYZ
Trim the spaces out and you get:
U||VWM||XYZ
which gives you more space. Also, why are you using double bars ||, is it possible to use a single?
Thats all I can suggest. Good luck.
Dont't leave it blank. Enter a Space or other character which your application can recognize and ignore.
update *TABLE* set *COLUMN* = "new_value" where name = *ROW NAME*