Re: updating 3 columns with a select clause

Maurice:

You are making this too hard.

There is nothing that says the update statement can't reference columns of the table that is being updated... and it uses the value of the current row. So, do this:

update update_test set
real_date = to_date(date_string,'YYYYMMDD'), count_date = length(date_string),
time_wo_sysdate = (sysdate - to_date(date_string,'YYYYMMDD'))

I do have a question.... why are you stroring computed values in the table? You could create a view that does this and you would not have to do the update at all.

HTH,
Tom Best

Maurice Samuels <samuels_at_blue.seas.upenn.edu> wrote in message news:8m6lik$95s$1_at_netnews.upenn.edu...
>
> if anyone could help with this one, i'd appreciate it:
>
> i have a table:
> ncc1701-e> desc update_test
> Name Null? Type
> ------------------------------- -------- ----
> DATE_STRING VARCHAR2(50)
> REAL_DATE DATE
> COUNT_DATE NUMBER
> TIME_WO_SYSDATE NUMBER
>
> for every value of date_string i want to update real_date, count_date,
 time_wo_sysdate using a
> data function on the date_string value. ie:
> date_string='20000701'
> i want real_date to = to_date(date_string,'YYYYMMDD')
> count_date to = length(date_string)
> time_wo_sysdate to = (sysdate - to_date(date_string,'YYYYMMDD')
>
> how can i do this in an update statement?
> i was thinking of using something like:
> update update_test
> set real_date = (select to_date(date_string,'YYYYMMDD') from update_test),
> count_date = (select length(date_string) from update_test),
> time_wo_sysdate = (select (sysdate - to_date(date_string, 'YYYYMMDD'))
 from update_test)
> where
> date_string like '2000%'
> and real_date is null;
>
> this works if the update_test table has one row, how can i make it work
 for many rows?
> also, how can i ensure that the values returned by the select statements
 match the row of the update statement
> (how would the rowid's match)?
>
> i know that i could do this in pl/sql with a for loop and matching the
 rowid's but i was wondering if i
> could do this as a simple update statement.
>
> thanks alot for any help in advance.
> -maurice
> samuels_at_seas.upenn.edu
Received on Tue Aug 01 2000 - 00:00:00 CDT