I am building a test dataset that associates various people with families, and all individual people have a unique ID, and each family (which has multiple people) has a unique ID.
I have created 2 tables, each with people in them that have unique "peopleID" values. The first table has unique "familyID" values. So:
The first table ("people1") looks like this:
person_ID | family_ID | first_name | last_name | date_of_birth | address
1 | 1 | John | Smith | 01-01-1901 | 123 Anywhere St
2 | 2 | Jane | Jones | 03-01-1982 | 312 Anyplace Ave
6 | 3 | Harold | Viking | 06-30-1973 | 283 Northman Rd
And the second table (people2) looks like this:
person_ID | family_ID | first_name | last_name | date_of_birth | address
3 | NULL | Richard | Hawkins | 04-20-2003 | NULL
4 | NULL | Juliet | Jordan | 03-02-2005 | NULL
And I want to add the rows from people2 to people1, each with a RANDOM familyID from people1, so the end result looks something like this:
person_ID | family_ID | first_name | last_name | date_of_birth | address
1 | 1 | John | Smith | 01-01-1901 | 123 Anywhere St
2 | 2 | Jane | Jones | 03-01-1982 | 312 Anyplace Ave
6 | 3 | Harold | Viking | 06-30-1973 | 283 Northman Rd
3 | 2 | Richard | Hawkins | 04-20-2003 | NULL
4 | 3 | Juliet | Jordan | 03-02-2005 | NULL
One way I thought of to do this would be to build a reference table from the second table, like so:
SELECT person_ID, (select family_ID from people1 ORDER BY RANDOM() LIMIT 1) as family_ID from people2;
But that returns a single random entry from "people1" for every row in "people2." How could I force it to return a DIFFERENT random value for every row? A cursor that iterates through each row in "people2" and selects a different familyID value from "people1," perhaps? How would that be written?
Aucun commentaire:
Enregistrer un commentaire