Using Rails update_all method to update fields with value from another column

Question

I'm trying to update a field in using update_all. However I need the value to be taken from another field which is re-written to my specific format.

If I have something like this in my model:

 def self.clean_mac_address()
   clean_mac_address = :macaddress.gsub(/[^0-9a-z]/i, '')
 end

When I run this:

 Radacct.update_all("mac_clean = #{clean_mac_address}")

I get an error:

 NoMethodError: undefined method `gsub' for :macaddress:Symbol

Any thoughts how I can do this? Or is there a simpler way to update the field?

I doubt if you can use gsub method on a symbol. May be thats the reason you are getting error. — uday
– uday, Commented Feb 1, 2012 at 8:58
First, why are you calling gsub on a symbol? Second, if I understand correctly you want to calculate and fill mac_clean column based on other column for every record. In that case you can use update_all only if you perform all manipulation in SQL as update_all will run the update you're passing to it as a string on all records, but it won't load them from DB and won't initialize model instances. That means you're dealing with SQL queries and do not have direct access to ActiveRecord attributes. — KL-7
– KL-7, Commented Feb 1, 2012 at 8:58
@KL7 :macaddress isn't supposed to be a symbol - it should be a string. So, I have to write something to loop through each record and update?? Am a bit confused. — simonmorley
– simonmorley, Commented Feb 1, 2012 at 9:04
Main difference between a symbol and string is that symbol is unique and immutable. We generally use symbol to represent something, eg: as the key of a hash in parameters params[:id]. What is :macaddress in above code? — nkm
– nkm, Commented Feb 1, 2012 at 9:13
It's actually sent to db by our radius server and is a record of which locations our customers are calling in from. Because it has the wrong format, I need to rewrite it but determined this would be better at authentication time. Therefore, all new sessions have this field entered in correct format. However, I'm left with several thousand fields with no value. — simonmorley
– simonmorley, Commented Feb 1, 2012 at 9:33

Frederick Cheung · Accepted Answer · 2012-02-01 20:45:08Z

21

update_all generates a single SQL query to run - it can't do clever stuff like change arbitrary bits of ruby into equivalent SQL.

You either need to load all you instances (via find_each for example) and fix them one by one (ie don't use update_all), for example

Foo.find_each do |foo|
  # update foo here
  foo.save!
end

Or find a way of expressing that cleaning operation in SQL. For example Postgres has a regexp_replace function

Foo.update_all("some_column = regexp_replace(some_column, 'your_regexp_here', '','g')")

Which would remove everything replacing that regexp. Obviously you'll need to check the documentation for your database to see whether it supports such a feature.

edited Feb 1, 2012 at 20:45

answered Feb 1, 2012 at 10:08

Frederick Cheung

84.3k9 gold badges157 silver badges175 bronze badges

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

4 Comments

simonmorley Over a year ago

Do I need to stick this in a loop to find record A, replace value, move on etc. etc?? Can you share some code? S

Frederick Cheung Over a year ago

Depends on which of the 2 ways I suggested you choose

simonmorley Over a year ago

Ok, ill look into it. Am on mysql. S

makhan Over a year ago

I wish I could upvote this multiple times. Every time I have to write a batch update I end up googling for this method name.

a3y3 · Accepted Answer · 2018-02-12 14:53:50Z

0

While the accepted answer provides a nice way to update_all, what I'd use is

read_with_clean_addr = Radacct.where(mac_clean: :macaddress.gsub(/[^0-9a-z]/i, ''))
read_with_clean_add.update_all(mac_clean: "#{clean_mac_address}")

answered Feb 12, 2018 at 14:53

a3y3

1,2552 gold badges13 silver badges38 bronze badges

Collectives™ on Stack Overflow

Using Rails update_all method to update fields with value from another column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related