Solved

Oracle. Replace or remove data from one column

Posted on 2009-06-30
14
940 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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Latency in .net app using DB in .net 21 36
How to SQL Trace a SPECIFIC query 24 59
subtract 1 in Access 2003 query 7 39
Process mapping 5 31
Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

863 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

23 Experts available now in Live!

Get 1:1 Help Now