I have a workflow table which has patient_id, pat_firstname, pat_lastname data in it. I want to extract this data into a patient table.
INSERT INTO patients (patient_id, first_name, last_name)
SELECT DISTINCT patient_id, pat_firstname, pat_lastname
FROM workflow
The problem I am having is that this doesn't work because patient_id is the primary key in the patients table and some first_name fields or last_name fields don't match so there's duplicates trying to be inserted.
What I want is to just use any of the records from the workflow table for a first_name and last_name. For example, if there's:
patient_id = 2
pat_first_name = Kris
pat_last_name = Doman
patient_id = 2
pat_first_name = Kristofer
pat_last_name = Doman
I want to insert either record, it doesn't matter. How can I accomplish this in a single insert statement?