On Tuesday, October 1, 2013 2:37:49 PM UTC-7, Rodrigo Rosenfeld Rosas wrote:
> Are there any built-in API to allow batch updates with support for
> PostgreSQL?
>
> I'm looking for something like Dataset#import.
>
> Here's how the batch update sql could look like:
>
> UPDATE table_name SET col1 = v.c1, col2 = v.c2
> FROM (VALUES (10, 'a', 200), (20, 'c', 300)) AS v (id, c1, c2)
> WHERE table_name.id = v.id;
>
>
Sequel doesn't have direct support for PostgreSQL's VALUES clause, or
aliasing with renamed columns, so the best you could do currently is
something like this:
rows = [[10, 'a', 200], [20, 'c', 300]]
DB[:table_name, Sequel.lit('(VALUES ?) AS v(id, c1, c2))',
Sequel.lit(rows.map{|r| DB.literal(r)}.join(',
')))].where(:table_name__id=>:v__id).update(:col1=>:v__c1, :col2=>:v__c2)
It's certainly possible to extend the DSL to allow something like this:
DB[:table_name, Sequel.pg_values(rows).as(:v, :cols=>[:id, :c1,
:c2])].where(:table_name__id=>:v__id).update(:col1=>:v__c1, :col2=>:v__c2)
However, I'm not sure if it is worth it considering that the use case is
fairly uncommon.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/groups/opt_out.
