MySQL INSERT INTO ... SELECT with subquery condition

Question

I need to export all the records with a duplicate value in a field to a different table. Namely all records where code appears more than once must be copied.

This is the query I tried:

INSERT INTO alternatives
SELECT * FROM winter WHERE code IN 
    (SELECT code
     FROM winter
     GROUP BY code
     HAVING COUNT(*) > 1)

but nothing happens, the mysql client hangs indefinitely until I stop it, and no records appear in the alternatives table. What am I doing wrong?

Alternatively, is there a better way to do what I need?

That is not the proper MySQL syntax for an INSERT - you need to specify VALUES. dev.mysql.com/doc/refman/5.6/en/insert.html — user3741598
– user3741598, Commented Feb 3, 2015 at 17:06
That's not the problem, the INSERT is fine (it works without the IN condition), the SELECT with the subquery doesn't work even without the initial INSERT — Matteo Riva
– Matteo Riva, Commented Feb 3, 2015 at 17:08
can you tell me for what purpose you have put " HAVING COUNT(*) > 1" ? — Dhruv Kapatel
– Dhruv Kapatel, Commented Feb 3, 2015 at 17:15
I need to export the duplicate records, so I group by code and look for those where that code appears more than once — Matteo Riva
– Matteo Riva, Commented Feb 3, 2015 at 17:16
There is no problem in your query unless alternatives and winter has different table structure. wait, you said it's work without IN condition? — Hotdin Gurning
– Hotdin Gurning, Commented Feb 3, 2015 at 17:38

Hotdin Gurning · Accepted Answer · 2015-02-03 18:09:55Z

1

Actually, your query should work. I don't know, but you can try INNER JOIN instead IN clause. something like :

-- INSERT INTO alternatives
SELECT * FROM winter
INNER JOIN
    (SELECT code
     FROM winter
     GROUP BY code
     HAVING COUNT(code) > 1) DUP ON winter.code = DUP.code

First, try it without INSERT statement. If works, you know what to do.

answered Feb 3, 2015 at 18:09

Hotdin Gurning

1,8193 gold badges16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matteo Riva Over a year ago

This version with the JOIN works, still curious about why the one with the subquery doesn't

Collectives™ on Stack Overflow

MySQL INSERT INTO ... SELECT with subquery condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related