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