SQL query does not work when executing from Rails console

Question

I am trying to run the following line in my Rails console:

ActiveRecord::Base.connection.execute "DELETE FROM recommendations WHERE place_id=4000000001267 AND recommender_id=2728498;UPDATE IGNORE recommendations SET place_id=4000000001267 WHERE place_id=4000000564630 AND recommender_id=2728498;"

It is simply deleting an old record and rewriting another record for merging purposes.

Rails console returns the following:

DELETE FROM recommendations WHERE place_id=4000000001267 AND recommender_id=2728498;UPDATE IGNORE recommendations SET place_id=4000000001267 WHERE place_id=4000000564630 AND recommender_id=2728498; ActiveRecord::StatementInvalid: Mysql2::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 IGNORE recommendations SET place_id=4000000001267 WHERE place_id=40000005' at line 1: DELETE FROM recommendations WHERE place_id=4000000001267 AND recommender_id=2728498;UPDATE IGNORE recommendations SET place_id=4000000001267 WHERE place_id=4000000564630 AND recommender_id=2728498; from /Users/huynguyen/.rvm/gems/ruby-1.9.3-p0/gems/activerecord-3.2.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:243:inquery'`

However, when I run this exact query in Sequel Pro, the query runs perfectly fine. Any idea what is going on?

@Greg To ignore any errors and continue since I'll be running multiple queries. — Huy
– Huy, Commented Nov 9, 2012 at 0:35
I encountered something similar in the past, see stackoverflow.com/questions/11127980/… still never reached a definitive answer, but my best guess is that there's some SQL translation going on when the server is running to better match SQL to the vendor. — Noz
– Noz, Commented Nov 9, 2012 at 0:47
@CyleHunter I had to change my code to do two queries. See answer below — Huy
– Huy, Commented Nov 9, 2012 at 6:48

Anton · Accepted Answer · 2012-11-09 01:50:33Z

2

Might it be connected to the fact AR's mysql adapter doesn't have multiple query execution on by default?

A quick fix for this would be splitting the query into two distinct and execute them one after another. And wrapping them into a transaction, of course :)

answered Nov 9, 2012 at 1:50

Anton

2,4832 gold badges23 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

SQL query does not work when executing from Rails console

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related