How to update existing record if the one being saved has the same key?

Question

MySQL has a very nice option for INSERT statement, which is particularly helpful for join tables without the id column. It inserts a record, but, instead of throwing an error if its key clashed with the existing one, that record is updated. Here's an example:

INSERT INTO table (key1,key2,data) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE data=3;

How to achieve the same with Active Record? The resultant code would then look like this:

class Model < ActiveRecord::Base
  belongs_to :key1
  belongs_to :key2
end

record = Model.new
record.key1 = key1
record.key2 = key2
record.data = 'new data'
record.WHAT?    #Inserts or updates `data` for the existing record

Answer 1

First of that's a MySQL-specific solution, as far as I know that syntax doesn't work with any other SQL-servers. Usually called Upsert in discussions.

I'd go with making a new method called save_or_update and then if the save fails, with a duplicate key exception, load and update_params.

Answer 2

You can do the following:

record = Model.find_or_create_by_key1_and_key2(:key1 => key1, :key2 => key2)
record.update_attribute(:data, "new data")

EDIT

zaius idea looks better, you should use find_or_initialize_by to avoid multiple saves, as he said :]

Answer 3

The answer by j is the right idea, but you probably want to go with find_or_initialize_by to avoid multiple saves on the object. E.g.

record = Model.find_or_initialize_by_key1_and_key2 new
record.data = 'new data'
record.save

By the sounds of it, you also want a validation to check that the model doesn't allow duplicate joins too:

class Model < ActiveRecord::Base
  belongs_to :key1
  belongs_to :key2
  validates_uniqueness_of :key1, :scope => :key2
end

Answer 4

You may want to take a look at composite primary keys. It's a plugin for ActiveRecord.