CODEWITHSUNDEEP
mysql
Noam B.
Noam B.

Reputation: 3250

mysql syntax error with a query that works directly

I am trying to execute a query in rails with: 

ActiveRecord::Base.connection.execute(query.join)

I build the query as a string and it looks like this:

UPDATE balance_lines SET parent_id = 2728740 WHERE id = 2728797;
 UPDATE balance_lines SET parent_id = 2728725 WHERE id = 2728767;
 UPDATE balance_lines SET parent_id = 2728735 WHERE id = 2728783;
 UPDATE balance_lines SET parent_id = 2728727 WHERE id = 2728770;
 UPDATE balance_lines SET parent_id = 2728751 WHERE id = 2728838;

When executing the query from rails i get an 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 balance_lines SET parent_id = 2728725 WHERE id = 2728767;

But when I execute it from the mysql UI - it works! what is the problem?

Upvotes: 0

Views: 65

Answers (1)

echo_Me
echo_Me

Reputation: 37233

try that with CASE statment:

  UPDATE balance_lines SET parent_id = 
         CASE WHEN id = 2728797  THEN 2728740
              WHEN id = 2728767  THEN 2728725
              WHEN id = 2728783  THEN 2728735
              WHEN id = 2728770  THEN 2728727
              WHEN id = 2728838  THEN 2728751

         END
  WHERE id  in (2728797,2728767,2728783,2728770,2728838)

Upvotes: 3

Related Questions