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

Question

I am trying to update a field using update_all. However, I need the value to be taken from another field, which is being overwritten in 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 ran this:

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

I get an error message:

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

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

Answer 1

update_all generates one SQL query to run - it cannot do smart things, such as changing arbitrary ruby ​​bits to equivalent SQL.

You need to download all instances (e.g. via find_each ) and fix them one at a time (i.e. don't use update_all), e.g.

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

Or find a way to express this cleanup operation in SQL. For example, Postgres has a regexp_replace function regexp_replace

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

To remove everything, replacing this regular expression. Obviously, you need to check the documentation for your database to see if it supports such a function.

Answer 2

Although the accepted answer provides a good way to update_all , I would use

 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}")