insert into table foo from select query from table bar

Question

Suppose I had two MySQL tables, foo and bar. Table foo is like this:

mysql> desc foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| biz  | varchar(255)  | NO   | PRI |         |       |
| buzz | mediumtext    | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

table bar is like this:

mysql> desc bar;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| quux          | varchar(255)     | NO   | UNI |         |                |
| blah          | varchar(255)     | NO   |     |         |                |
+---------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Now suppose I wanted all of bar.quux to be inserted into foo.biz and all of bar.blah to be inserted into foo.buzz. Why can't I do something like this:

insert into foo (biz, buzz) values (select quux as biz, blah as buzz from bar);

Is there a way to DWIM without recourse to a script?

Answer 1

The way to DWIM is to read the documentation for INSERT syntax.

If you use the INSERT INTO <table> VALUES (...) form, then you specify one or more row constructors in parentheses.

If you use the INSERT INTO <table> SELECT ..., then you don't use the VALUES clause. The rows come from the SELECT statement.

In your case, don't use VALUES or parentheses.

insert into foo (biz, buzz) 
select quux as biz, blah as buzz from bar;

P.S.: It does no harm to assign column aliases in your SELECT query, but it's not necessary. The column names in the select-list are ignored.