mercredi 25 novembre 2015

SQL: Populate a date-column with random test data - how do I make a subquery run for each record in a table?

So I've got an oracle 11g SQL db. It has a table which contains a date-field. I just added the field, so it currently contains no data.

╔═════════╦═══════════╗
║ some_id ║ some_date ║
╠═════════╬═══════════╣
║ 1       ║ null      ║
╠═════════╬═══════════╣
║ 2       ║ null      ║
╠═════════╬═══════════╣
║ 3       ║ null      ║
╚═════════╩═══════════╝

I wrote a quick query to generate some test-data in our testing environment:

update some_table 
set some_date =(SELECT TO_DATE( TRUNC( DBMS_RANDOM.VALUE(TO_CHAR(DATE '2015-12-01','J'),TO_CHAR(DATE '2016-08-01','J'))),'J') FROM DUAL)
where some_id = any(select some_other_id from vw_some_complicated_view);

Now this sub-query, probably not so elegantly, generates a random date in a range:

SELECT TO_DATE( TRUNC( DBMS_RANDOM.VALUE(TO_CHAR(DATE '2015-12-01','J'),TO_CHAR(DATE '2016-08-01','J'))),'J') FROM DUAL

So I want this subquery to run for each record in the table... in other words I want to set every date to be a random date - but not the same random date.

What I got:

╔═════════╦═══════════╗
║ some_id ║ some_date ║
╠═════════╬═══════════╣
║ 1       ║ 08-DEC-15 ║
╠═════════╬═══════════╣
║ 2       ║ 08-DEC-15 ║
╠═════════╬═══════════╣
║ 3       ║ 08-DEC-15 ║
╚═════════╩═══════════╝

What I want:

╔═════════╦═══════════╗
║ some_id ║ some_date ║
╠═════════╬═══════════╣
║ 1       ║ 04-JAN-16 ║
╠═════════╬═══════════╣
║ 2       ║ 20-DEC-15 ║
╠═════════╬═══════════╣
║ 3       ║ 03-MAR-16 ║
╚═════════╩═══════════╝

How can I accomplish this, preferably in a normal SQL query format. I looked at other answers where they generate (non-date) random data, and they just use this format without issues.

Thanks!




Aucun commentaire:

Enregistrer un commentaire