mysql insert into query not working

Question

The query is pasted below. Basically I have a users table, and I want to create 1 record for every entry in the users table in the controls table. I need the user_id from the users table when I'm inserting into the controls table. I get a syntax error when I try and run the query below. I've looked at a bunch of other MySQL examples that do something similar but get the same error. I'm running MySQL 5.6.21.

This will end up being a Rails migration, so I am also amenable to using ActiveRecord, but haven't really figure out how to do something like this with it.

INSERT into controls (default, whitelist, user_id, admin_status, system_status, created_at, updated_at) values (0, 0, (SELECT id FROM users), 'inactive', 'sleeping', DATE(NOW), DATE(NOW));

DEFAULT is a reserved word in MySQL. The error message would have been a clue. Also, you can't use SELECT that way. See INSERT... SELECT syntax. — Strawberry
– Strawberry, Commented Jun 29, 2015 at 23:15

Mike K · Accepted Answer · 2015-06-29 23:25:01Z

2

I believe your problem is that you're trying to mix a SELECT with VALUES (which is used for inputting literal values).

Try:

INSERT into controls
    (`default`, whitelist, user_id, admin_status, system_status, created_at, updated_at)
    SELECT 0, 0, id, 'inactive', 'sleeping', NOW(), NOW() FROM users;

edited Jun 29, 2015 at 23:25

answered Jun 29, 2015 at 23:15

Mike K

4863 silver badges7 bronze badges

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

Comments

spencer7593 · Accepted Answer · 2015-06-29 23:26:07Z

DEFAULT is a MySQL reserved word. You may need to enclose that column name in backticks.

INSERT into controls (`default`, whitelist, 
                      ^       ^

Those are backtick characters (key to the left of the 1/! key), not single quotes.

The error message from MySQL should indicate where in the SQL text MySQL thinks the problem is.

If this is a new table, strongly consider using a column name that is not a MySQL reserved word.

Reference: https://dev.mysql.com/doc/refman/5.5/en/keywords.html

The SELECT in the context of the VALUES clause needs to return at most one row. You'd need to ensure that SELECT doesn't return more than one row, e.g. add a LIMIT 1 clause. But

Reading your question again... if you want to insert a row in the new controls table for every row that's in users table, don't use the VALUES keyword. Use the INSERT ... SELECT form of the INSERT statement.

Reference: http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

Collectives™ on Stack Overflow

mysql insert into query not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related