CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecord
Shail Patel
Shail Patel

Reputation: 1814

mysql insert into query not working

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));

Upvotes: 1

Views: 2160

Answers (2)

spencer7593
spencer7593

Reputation: 108390

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

Upvotes: 1

Mike K
Mike K

Reputation: 486

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;

Upvotes: 2

Related Questions