CODEWITHSUNDEEP
mysqlsqlselectinsertdistinct
Kristofer Doman
Kristofer Doman

Reputation: 439

mySQL Insert Into with Select

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?

Upvotes: 3

Views: 120

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

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

Upvotes: -1

Asenar
Asenar

Reputation: 7010

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

Upvotes: 2

Related Questions