3

I have been looking for a solution to this problem since couple of hours now. It seems very simple but for some reason I can't get it to work.

I have a model, lets call it MyModel. It has 3 columns, :id, :column_a, :relationship_id

:relationship_id is a foreign key with not null constraint.

I have 4 records in the database already like such:-

:id, :column_a, :relationship_id,
1, 1, 7
2, 2, 8
3, 3, 9
4, 4, 10

I want to change the values of :column_a, using the following data

data = [{"id"=>"1", "column_a"=>4},
        {"id"=>"2", "column_a"=>3},
        {"id"=>"3", "column_a"=>2},
        {"id"=>"4", "column_a"=>1}]

But this data is missing :relationship_id column values which I want to keep unchanged.

When I do something like MyModel.upsert_all(data, update_only: :column_a) I get the following error

ActiveRecord::NotNullViolation: PG::NotNullViolation: ERROR:  null value in column "relationship_id" of relation "my_models" violates not-null constraint
DETAIL:  Failing row contains (1, 4, null).

I have tried update_all and raw SQL but the problem remains the same. The only way I can get it working is by individually updating each record which is not efficient.

Maybe I am doing something wrong? Any ideas?

Hussain Niazi
  • 579
  • 1
  • 3
  • 21
  • "upsert" means attempt an insert and if there's a conflict then update the existing row instead. You can't attempt to do the insert portion of the upsert if you aren't passing all the required values. Are you able to pass a `relationship_id` value in your `data`? – cschroed Nov 29 '22 at 02:05
  • Yes I understand that. Unfortunately getting relationship_id will be a difficult and inefficient solution. I wonder why there is no way to bulk update only specific columns :/ – Hussain Niazi Nov 29 '22 at 07:00
  • 1
    Can't you just do one more query before you do the update with `MyModel.where(id: data.map { |row| row['id'] }).pluck(:id, :relationship_id).to_h` and then add the `relationship_id` values into your `data` array? – cschroed Nov 29 '22 at 14:06
  • Yes. I am implementing this. By far this looks like the only solution – Hussain Niazi Nov 29 '22 at 14:07
  • Yeah, otherwise, if there's no pattern to updates you want to make then I think the only option would be to try to build raw SQL that does something like the answers to [this question](https://stackoverflow.com/q/62888585/2622934). – cschroed Nov 29 '22 at 14:16

0 Answers0