CODEWITHSUNDEEP
mysqlrubyruby-on-rails-3.2update-all
Eugene Matveyev
Eugene Matveyev

Reputation: 161

update_all with string concatenate

I'm trying to update the 'allowed_ips' field for all my users, but it's not working as I expect.

'allowed_ips' is a string with ip-addresses.

User.where(role: 1).update_all(:allowed_ips => ["CONCAT('allowed_ips', '192.168.0.1')"])

# output:
> user.allowed_ips
 => "CONCAT('allowed_ips', '192.168.0.1')"

What I want is:

UPDATE 'users' SET 'allowed_ips' = CONCAT('allowed_ips', '192.168.0.1') WHERE 'role' = '1'

# output:
> user.allowed_ips
 => "127.0.0.1, 127.0.0.2, 192.168.0.1"

Upvotes: 1

Views: 2348

Answers (2)

Vamsi Krishna
Vamsi Krishna

Reputation: 3792

The CONCAT should not be in quotes. The allowed_ips inside CONCAT should not be in quotes.

User.where(role: 1).update_all("'allowed_ips' = CONCAT(allowed_ips, ', 192.168.0.1')")

Upvotes: 1

Alexey Kharchenko
Alexey Kharchenko

Reputation: 1032

This should do the trick:

User.where(role: 1).update_all(["allowed_ips = CONCAT(allowed_ips, ?)", '192.168.0.1'])

In your case "CONCAT..." is considered to be just a string value for the updated field.

BTW, you probably need to fix the CONCAT call itself as well, or the strings will be glued together without the delimiters.

Also check serialize (link) for storing objects (arrays for example) in the ActiveRecord model attributes. This might be a cleaner way to get what you want to achieve

Upvotes: 6

Related Questions