Clicky

Dear fellow developers:

I have the following SQL query for Oracle that works just fine:

 UPDATE OWNER set TIME = SUBSTR(OTHERINFO, INSTR(OTHERINFO, ' at ') + 4, 12), OTHERINFO = SUBSTR(OTHERINFO, 12, INSTR(OTHERINFO, ' at ') - 12), DATE = TO_DATE(SUBSTR(OTHERINFO, 12, INSTR(OTHERINFO, ' at ') - 12),'Day, Month DD, YYYY')                             
1: 

Select allOpen in new window



I would like to modify this SQL query so that after it completes, it finally deletes the contents of the "OTHERINFO" column in the table.  How would I do this?

Thanks in advance to all who reply.

asked 12/14/2011 03:46

fsyed's gravatar image

fsyed ♦♦


6 Answers:
Just don't set it?

you have:
UPDATE ...
 OTHERINFO = SUBSTR(OTHERINFO, 12, INSTR(OTHERINFO, ' at ') - 12
...

Make it null

UPDATE OWNER set TIME = SUBSTR(OTHERINFO, INSTR(OTHERINFO, ' at ') + 4, 12), OTHERINFO = null, DATE = TO_DATE(SUBSTR(OTHERINFO, 12, INSTR(OTHERINFO, ' at ') - 12),'Day, Month DD, YYYY')
link

answered

slightwv's gravatar image

slightwv

Should I not put the statement:



at the end, so that the steps are done sequentially, and that the column is set to null at the end, or does it matter?
link

answered 2011-12-14 at 11:49:46

fsyed's gravatar image

fsyed

>>at the end

Updates work on 'before' and not 'during' values.   Values are not changed sequentially as the data is processed.

I suggest you create simple test cases in a development database to experiment with ideas.

Run the simple test below in a development database and see what you get.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
drop table tab1 purge;

create table tab1(
	col1 varchar2(10),
	col2 varchar2(10)
);


insert into tab1 values('Hello','World');
commit;


update tab1 set col1=substr(col2,1,1), col2=substr(col1,2,2);

select * from tab1;
link

answered 2011-12-14 at 11:54:15

slightwv's gravatar image

slightwv

You're on a roll!  Your code worked yet again.  Thanks for your help!
link

answered 2011-12-14 at 12:00:07

fsyed's gravatar image

fsyed

>>> so that the steps are done sequentially

no,  all steps of the update happen effectively simultaneously.

the values of the columns for use at input are set when the row is read
that's why slightwv's example works

and also why you can do things like   UPDATE sometable set my_column = my_column + 4;

if my_column value wasn't "fixed" then that statement would be a loop.
link

answered 2011-12-14 at 12:07:43

sdstuber's gravatar image

sdstuber

Thanks ststuber for pointing that out!
link

answered 2011-12-14 at 12:09:53

fsyed's gravatar image

fsyed

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×51
×17
×132

Asked: 12/14/2011 03:46

Seen: 201 times

Last updated: 12/14/2011 04:07