• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

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)
1 Solution
Haris DjulicCommented:
can you update it  with new value like this:

update *TABLE*  set  *COLUMN* = "new_value"  where name = *ROW NAME*
update orders set bin = ( select binfrom from zones where orders.BIN = zones.BINTO )

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?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

stopmAuthor Commented:
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

stopmAuthor Commented:
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.
stopmAuthor Commented:
Here is the error message i get if text in  DATA column is tool large 'ORA-01401: inserted value too large for column"
Why are you limiting the VARCHAR to 254?

This seems like a self-imposed application design problem. A VARCHAR can be 4000 characters.

The other other error you are discussing: 'ORA-01401: inserted value too large for column

is simple, you are trying to append more of those data elements in a column that is already full.

If there is no application requirement for a 254 character line, I would alter the table. Would 1000 characters be sufficient?

alter table t modify data varchar2(1000);

Where t is your table name.
stopmAuthor Commented:
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.
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:


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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now