1

Let's assume, I have the following schema,

type Users struct {
    ID int gorm:"column:id;primaryKey;"
    Name string gorm:"column:"name;"
    IsActive bool gorm:"column:is_active;"
    Remarks string gorm:"column:"remarks;"
}

I have the ids (primary key), is_active and remarks with me. Now I wanted to update these values for different ids in bulk.

I was able to see a thread related to this in, but it is not associated with gorm. Updating multiple rows with different primary key in one query in PostgreSQL?

What I'm trying to achieve is to do the following:

Instead of updating one by one,

UPDATE users SET is_active = true, remarks = 'activated_by_cron' where id = 1;
UPDATE users SET is_active = false, remarks = 'deactivated_by_cron' where id = 2;
UPDATE users SET is_active = true, remarks = 'activated_by_user' where id = 3;

`

Update everything in a single query,

update users as u set
    is_active = c.is_active,
    remarks = c.remarks
from (values
    (1, true, 'activated_by_cron'),
    (2, false, 'deactivated_by_cron'),
    (3, true, 'activated_by_user')
) as c(id, is_active, remarks)
where c.id = u.id

My temporary solution was to write a custom utility function to create a raw query as explained above and then pass it to gorm to execute. But I feel this can be implemented in a better way.

Have someone faced this challenge before? If yes, how did you solve it?

pilotpin
  • 137
  • 5
Akshay P J
  • 11
  • 4
  • Maybe you can use `ON CONFLICT ... DO UPDATE SET ...`. https://gorm.io/docs/create.html#Upsert-x2F-On-Conflict – Trock Mar 13 '23 at 09:10
  • Why not just do it in a transaction and use the GORM update semantics? db.Transaction(func(tx *gorm.DB) ( loop })? – pilotpin Mar 14 '23 at 11:58
  • My other related DB operations happen in bulk. So If I use a loop, this will be a bottleneck, and it will affect the total execution time. @pilotpin – Akshay P J Mar 15 '23 at 11:49

0 Answers0