CODEWITHSUNDEEP
sqloracle-databaseoracle11g
HardLeeWorking
HardLeeWorking

Reputation: 195

How do I create a new column filled with the results of a query?

I have a column that is VARCHAR2 and the string inlcudes a date and time. I have extracted the date and now wish to populate a new column solely with the date and time.

So far I have:

alter table t
add cb_time
as
select substr(t.notes, 24, INSTR(t.notes, 'for')-1)
from Mytable t

this results in error ORA 2000 - missing ( keyword

Upvotes: 0

Views: 161

Answers (5)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

Since you are on 11g, and as you say that both columns are in same table. I would suggest, do not add a static column, rather add a virtual column.

So, you need not worry about the insert too. Oracle would give you the computed value without actually storing it.

Just add the virtual column as -

EDIT A closing brace was missing in the end.

ALTER TABLE t ADD
       column_name GENERATED ALWAYS AS     (to_date(substr(t.notes, 24, INSTR(t.notes, 'for')-1))) VIRTUAL;

NOTE You need to be cautious though. The virtual column would only be able to work if the expression is correctly evaluated. In this case, if the datetime literal is malformed, then the virtual column would fail. Either make sure that all the values in your base column have proper format of datetime literal.

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

An alternative method is to create CB_TIME as a virtual column. To do soan ALTER TABLE similar to the following is used:

ALTER TABLE T
  ADD CB_TIME DATE GENERATED ALWAYS AS
        (TO_DATE(SUBSTR(t.notes, 24, INSTR(t.notes, 'for')-1))) VIRTUAL;

Because virtual columns can't be INSERTed or UPDATEed, doing it this way means that the NOTES field must always contain a valid date string.

Share and enjoy.

Upvotes: 2

Onur Cete
Onur Cete

Reputation: 273

You can try;

alter table t add cb_time date;
update t set cb_time = to_date(substr(t.notes, 24, INSTR(t.notes, 'for')-1);

Upvotes: 0

David Faber
David Faber

Reputation: 12495

You would first add the column with ALTER TABLE and then issue an UPDATE:

ALTER TABLE t
  ADD cb_time DATE;

UPDATE t
   SET cb_time = SUBSTR(t.notes, 24, INSTR(t.notes, 'for')-1);

But you should also convert the value to a DATE at the same time:

UPDATE t
   SET cb_time = TO_DATE(SUBSTR(t.notes, 24, INSTR(t.notes, 'for')-1));

It might even be a good idea to make sure that the value in t.notes contains a date (maybe using regex) so you don't get an error when updating.

Upvotes: 0

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

You should do it as two separate operations.

alter table t add cb_time date;

update t set cb_time = to_date(substr(...

Upvotes: 0

Related Questions