Apparently, I can't edit previous messages. Here is the reworked version that allows you to reference the same column in the lookup as in the update.
https://gist.github.com/Aryk/ea6d9bce063a502323e3f42910925159 On Sunday, August 13, 2023 at 9:58:24 AM UTC-7 aryk....@gmail.com wrote: > Thanks Jeremy. I continued to be impressed with the Sequel library! > > For anyone else who wants it... not sure if this has a place in the > library but it is much more straight-forward and faster than iterating > through records on migrations or using complicated insert_conflict logic. > > Here is what I have so far... please test yourself as it's not battle > tested! > > # Use like this: User.batch_update([[{id: 4}, {full_name: "New Full > Name"}]]) > def self.batch_update(data_lookup, return_sql: false) > if data_lookup.present? > lookup_columns, update_columns = data_lookup.first.map(&:keys) > from, to = Sequel[:data], Sequel[table_name] > conditions = lookup_columns.each.with_object({}) { |col, h| > h[to[col]] = from[col] } > updates = update_columns.each.with_object({}) { |col, h| h[col] = > from[col] } > values = data_lookup.map { |l, u| l.values + u.values } > > ds = db. > from( > to.value.to_sym, > db.values(values).as(from.value.to_sym, [*lookup_columns, > *update_columns]) > ). > where(conditions) > > ds.send(return_sql ? :update_sql : :update, updates) > end > end > > On Wednesday, August 9, 2023 at 4:24:47 PM UTC-7 Jeremy Evans wrote: > >> On Wed, Aug 9, 2023 at 11:35 AM aryk....@gmail.com <aryk....@gmail.com> >> wrote: >> >>> Hi Jeremy, >>> >>> I saw some historical posts about batch updates like this one: >>> >>> https://groups.google.com/g/sequel-talk/c/uV7PRbEvQj0/m/lCvZaLx8AAAJ >>> >>> It feels a little "hacky" to say insert this record with a unique >>> primary key that already exists...when you get an error, then update it. >>> >>> I've been using a solution like this: >>> >>> run <<-SQL >>> update user_expo_push_tokens as uept set -- postgres FTW >>> device_id = ud2.device_id >>> from (values >>> #{updates.join(", ")} >>> ) as ud2(installation_id, device_id) >>> where ud2.installation_id = uept.installation_id; >>> SQL >>> >>> This also works in the case where the values are not a primary key or >>> have a unique index to throw the "on duplicate key update" logic. >>> >>> Is there a way to accomplish this without having to drop to straight sql? >>> >> >> I think this will work: >> >> DB.from(Sequel.as(:user_expo_push_tokens, :uept), >> DB.values(updates).as(:ud2, [:installation_id, :device_id])). >> where{{ud2[:installation_id]=>uept[:installation_id]}}. >> update(:device_id => Sequel[:ud2][:device_id]) >> >> SQL: >> >> UPDATE "user_expo_push_tokens" AS "uept" >> SET "device_id" = "ud2"."device_id" >> FROM (VALUES ...) AS "ud2"("installation_id", "device_id") >> WHERE ("ud2"."installation_id" = "uept"."installation_id") >> >> It depends on what "updates" is supposed to be. >> >> Thanks, >> Jeremy >> > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/7392c230-f7eb-4ce8-80ed-41c6944695dbn%40googlegroups.com.
