Solved

Oracle. Replace or remove data from one column

Posted on 2009-06-30
14
948 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
ID: 24752091
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
ID: 24752540
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 32

Expert Comment

by:awking00
ID: 24754575
Can you post the relevant table structure with some sample data and what you expect the end result to look like?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:stopm
ID: 24759141
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
ID: 24759979
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
 

Author Comment

by:stopm
ID: 24759997
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
ID: 24776631
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
ID: 24778943
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
ID: 24781493
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
ID: 24792658
Dont't leave it blank. Enter a Space or other character which your application can recognize and ignore.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 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