Solved

Oracle. Replace or remove data from one column

Posted on 2009-06-30
14
935 Views
Last Modified: 2013-12-19
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)
0
Comment
Question by:stopm
14 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
Comment Utility
can you update it  with new value like this:

update *TABLE*  set  *COLUMN* = "new_value"  where name = *ROW NAME*
0
 
LVL 14

Expert Comment

by:shru_0409
Comment Utility
update orders set bin = ( select binfrom from zones where orders.BIN = zones.BINTO )

may be this help u or pls explain with example..
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you post the relevant table structure with some sample data and what you expect the end result to look like?
0
 

Author Comment

by:stopm
Comment Utility
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
0
 

Author Comment

by:stopm
Comment Utility
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:stopm
Comment Utility
Here is the error message i get if text in  DATA column is tool large 'ORA-01401: inserted value too large for column"
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 300 total points
Comment Utility
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.
0
 

Author Comment

by:stopm
Comment Utility
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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.

0
 
LVL 13

Expert Comment

by:anand_2000v
Comment Utility
Dont't leave it blank. Enter a Space or other character which your application can recognize and ignore.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now