Home » SQL & PL/SQL » SQL & PL/SQL » update question
update question [message #19236] Wed, 06 March 2002 09:13 Go to next message
Dave
Messages: 92
Registered: August 1999
Member
Is it possible to update part of a row and leave the rest intact. An example of what I mean is in 1 row I have data similar to below:

ANO=4162203,BNO=01234,DATE=20020131,DURATION=001628,EXC=DAB,RC=00,RECID=000001,RT=00,TIME=08541
ANO=3182345,BNO=01234,DATE=20010101,DURATION=000648,EXC=DAB,RC=00,RECID=000001,RT=00,TIME=08541

If the expression 'BN0=01234' is in the row I want to change it to another value but leave everything else the same as this data varies, is this possible?
Re: update question [message #19237 is a reply to message #19236] Wed, 06 March 2002 09:50 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Of course. I don't know if you're saying ANO, BNO, etc. are columns or if there is only one column with all that data in it.

In either case, it's possible.
UPDATE tablex
SET onlycolumn = (
SUBSTR(onlycolumn,1,INSTR(onlycolumn,'BNO=01234')-1)
||
SUBSTR
(onlycolumn,INSTR(onlycolumn,'BNO=01234')
+9,
LENGTH(onlycolumn)-(INSTR
(onlycolumn,'BNO=01234')
+9)
))
WHERE onlycolumn like '%BNO=01234%';
(this will leave two commas in a row where BNO=01234 had been)

or

UPDATE tablex
SET ANO = 0
WHERE BNO = '01234';
Re: update question [message #19298 is a reply to message #19236] Sat, 09 March 2002 16:56 Go to previous message
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
Member
I am not sure whether you are talking about a row consists of several columns or a column contains data stream lik a row. If the row you provided is a row of columns, that is, sure enough, you can use UPDATE command. The UPDATE command itself is meant for that. It updates the columns in rows whichever match your criteria.
But, if you are talking about a column which contains a stream of data, just like a row of data in one particular column, you can use REPLACE() function.
Examples:
1. A column of row =
UPDATE MY_TABLE SET COLUMN1 = 'NEW_COLUMN1_VALUE' WHERE COLUMN1 = 'BN=01234':

2. To change an offset or subset of a stream of data contained in a column
UPDATE MY_TABLE SET COLUMN1 = REPLACE(COLUMN1,'BNO=01234','NEW_VALUE');
This updates the COLUMN1 column in every row that matches the criteria.

Hope you understand my point. Still have trouble to understand this, come back with your sample code, I just tailor it to your requirement and send it back to you.
Good luck :)
Previous Topic: dsadas
Next Topic: Problem with Long datatype
Goto Forum:
  


Current Time: Thu Mar 28 11:13:27 CDT 2024