Inserting a GROUP BY result into another table

I was migrating a field to a new table. The new table has three fields. What I tried was

INSERT INTO foo VALUES ('', (SELECT bar FROM baz GROUP BY bar), '');

This resulted in an erro开发者_如何转开发r due to multiple rows resulting from the select.

What is the right way to do this?

If I understand you correctly, you want something like:

INSERT INTO foo (col1name, col2name, col3name)
    SELECT '', bar, ''
    FROM baz
    GROUP BY bar

Or if I'm understanding you correctly and you want one entry in the new table for every distinct bar value in the old table I think this makes that a bit clearer.

INSERT INTO foo (col2name) SELECT DISTINCT bar FROM baz

The execution plan and performance should be similiar

You can try:

INSERT INTO foo 
SELECT '',bar,'' FROM baz GROUP BY bar

INSERT INTO foo (fieldName1,fieldName2,fieldName3) 
    SELECT '',bar,'' FROM baz GROUP BY bar

Going with Michael's answer, another possibility would be

INSERT INTO foo (col2name) SELECT bar FROM baz GROUP BY bar

where col1 and col3 are defined to have a default of the empty string.