Moving data based on other fields in the record
Essentially this is my scenario: I am importing flat data from a spreadsheet into a relational database. There are "datapoints" that are raw pieces of data and "observations" based off of those datapoints. I have attached a screengrab of the database structure.
In the spreadsheet, datapoints are occasionally repeated and observations are unique. I have already imported the datapoints and observations and removed duplicate datapoints, and each datapoint and each observation has a unique serial. Now I want to match the datapoints and observations together in a parent/child relationship table. Because the datapoints are not unique I can't just use each original record and move the data around like that. I want to use the original spreadsheet data and do something like this (this syntax is entirely made up, but I hope it makes sense).
where temp_import::orig_datapoint = DataPoints::datapoint AND temp_import::orig_observation = Observations::observation_text PUT DataPoints::__datapoint_id_pk into ObservationsDatapoints::__datapoint_id_fk AND Observations::__observation_id_pk into ObservationsDatapoints::__observation_id_fk
I would also like to populate the ObservationTheme table in a similar way, but I imagine once I understand this I can just use the same tactic.