I have a column car_details
with 2000 entries, each of which is a hash of info that looks like this:
我有一个包含2000个条目的car_details列,每个条目都是这样的信息散列:
{"capacity"=>"0",
"wheels"=>"6",
"weight"=>"3000",
"engine_type"=>"Diesel",
"horsepower"=>"350",
"fuel_capacity"=>"35",
"fuel_consumption"=>"30"}
Some cars have more details, some have less. I want to rename the "fuel_consumption"
key to "mpg"
on every car that has that key.
有些车有更多的细节,有些车有更少的细节。我想将“fuel_consumption”键重命名为“mpg”键,用于每辆拥有该键的汽车。
2 个解决方案
#1
1
As far as I know, there is no easy way to update a serialized column in a data table en masse with raw SQL. The best way I can think of would be to do something like:
据我所知,用原始SQL对数据表中的序列化列进行整体更新是不容易的。我能想到的最好的办法是:
Car.find_each do |car|
mpg = car.car_details.delete("fuel_consumption")
car.car_details["mpg"] = mpg if mpg
car.save
end
This is assuming that you are using Active Record and your model is called "Car".
这是假设您正在使用活动记录,并且您的模型被称为“Car”。
#2
1
Well, a previous answer will generate 2000 requests, but you can use the REPLACE
function instead. Both MySQL and PostgreSQL have that, so it will be like:
以前的答案将生成2000个请求,但是您可以使用REPLACE函数。MySQL和PostgreSQL都有这种功能,所以会是:
Car.update_all("car_details = REPLACE(car_details, 'fuel_consumption', 'mpg')")
Take a look at the update_all
method for the conditions.
查看条件的update_all方法。
See also PostgreSQL string functions and MySQL string functions.
还可以看到PostgreSQL字符串函数和MySQL字符串函数。
#1
1
As far as I know, there is no easy way to update a serialized column in a data table en masse with raw SQL. The best way I can think of would be to do something like:
据我所知,用原始SQL对数据表中的序列化列进行整体更新是不容易的。我能想到的最好的办法是:
Car.find_each do |car|
mpg = car.car_details.delete("fuel_consumption")
car.car_details["mpg"] = mpg if mpg
car.save
end
This is assuming that you are using Active Record and your model is called "Car".
这是假设您正在使用活动记录,并且您的模型被称为“Car”。
#2
1
Well, a previous answer will generate 2000 requests, but you can use the REPLACE
function instead. Both MySQL and PostgreSQL have that, so it will be like:
以前的答案将生成2000个请求,但是您可以使用REPLACE函数。MySQL和PostgreSQL都有这种功能,所以会是:
Car.update_all("car_details = REPLACE(car_details, 'fuel_consumption', 'mpg')")
Take a look at the update_all
method for the conditions.
查看条件的update_all方法。
See also PostgreSQL string functions and MySQL string functions.
还可以看到PostgreSQL字符串函数和MySQL字符串函数。