Out of the box, you can’t run this against a mysql db from Rails:
ActiveRecord::Base.connection.execute(”
update table_name set field1=’value1′ where field2=’value2′;
update table_name set field1=’value3′ where field2=’value4′;
… many many more statements …
“)
In almost all cases it would be better to use ar-extensions with the duplicate key error update trick. Some colleagues of mine, who invented a plugin to invalidate find_by_sql unless you provide an excuse for each use, would put it more strongly than that.
But if, bearing in mind all the good reasons not to drop to sql from Rails, you still want to do it, and you want to execute multiple statements at once against a mysql db, you need to make a change to active_record/connection_adapters/mysql_adapter.rb. This is described in a different context in the Rails wiki, but the nutshell is, you have to add the flag for CLIENT_MULTI_STATEMENTS (65536) to the connection, so (in 2.1.0) the line:
ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket], config)
becomes
ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536], config)
Then, instead of getting the error
ActiveRecord::StatementInvalid: Mysql::Error: You
have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near ‘update
table_name set field1=’value3′ where field2=’value4” at line 1: update
table_name set field1=’value1′ where field2=’value2′;update table_name
set field1=’value3′ where field2=’value4′ ...
the updates will work as expected.
Tags: rails, ruby.