CODEWITHSUNDEEP
sqloracle-databasesql-update
user677526
user677526

Reputation:

Update table with a sequence of values

I'm looking for a way to update an existing table with an ascending sequence of dates. Using this select works:

SELECT
    TO_DATE('01-MAR-15') + (rownum * 2)
FROM all_tables
WHERE rownum <= 35

I cannot, however, use this in an update query. In Oracle, how could I write an update table statement similar to this?

UPDATE my_table
SET my_date_column = (
    TO_DATE('01-MAR-15') + (rownum * 2)
)
WHERE my_keys IN (
    // my subquery here - select my_key from my_table where....
)

Upvotes: 0

Views: 72

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You can use merge statement, like here:

merge into my_table m
using (
  select my_key, date '2015-03-01' + (rownum * 2) dt
    from my_table where my_key in (1, 3)) u
on (m.my_key = u.my_key)
when matched then update set my_date_column = u.dt;

SQLFiddle

Place your subquery in using clause. My_key has to be unique.

Upvotes: 2

Related Questions