3
votes

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?

2

2 Answers

2
votes

You have two options:

use INSERT IGNORE : this will not modify already existing values

INSERT IGNORE INTO patients (patient_id, first_name, last_name)
SELECT DISTINCT patient_id, pat_firstname, pat_lastname
FROM workflow

or you can use REPLACE : this will either insert, either update the datas.

REPLACE INTO patients (patient_id, first_name, last_name)
SELECT DISTINCT patient_id, pat_firstname, pat_lastname
FROM workflow

If saving ressources matters, REPLACE is not recommended.

check the mysql documentation for details about INSERT UPDATE and REPLACE

-1
votes

You can use GROUP BY to do this for you. You can change your SELECT portion of the query as follows:

SELECT patient_id, pat_firstname, pat_lastname
FROM workflow
GROUP BY patient_id