CODEWITHSUNDEEP
mysqldatabaseinsertduplicationinsert-select
jcubic
jcubic

Reputation: 66480

INSERT SELECT query when one column is unique

I need to call INSERT + SELECT to import user data to different table and I need to filter user_name duplications, So I need something like this:

INSERT INTO new_table SELECT email, distinct user_name, password from old_table

but distinct works only when using distinct email, user_name, password and all those column need to be unique.

Is there any other way to insert select with uniq user_names, (I need only first row - with lower id)?

EDIT I forget to mention that I use mysql

Upvotes: 2

Views: 400

Answers (1)

Eddy
Eddy

Reputation: 5360

Without testing I would expect something like this to do the trick (assuming the id column is named id)

INSERT INTO new_table 
    SELECT email, user_name, password 
    FROM old_table 
    INNER JOIN 
        ( SELECT MIN(id) FROM old_table GROUP by user_name ) minids
    ON minids.id = old_table.id

Upvotes: 2

Related Questions