Bug #99760 for SQL-Abstract-More: join tables in update

Thu Oct 23 13:38:48 2014 valkoles [...] gmail.com - Ticket created

Subject:	join tables in update
Date:	Thu, 23 Oct 2014 13:38:37 -0400
To:	bug-SQL-Abstract-More [...] rt.cpan.org
From:	val <valkoles [...] gmail.com>

Hi, Documentation for 'update' statement support declares that first argument should be a table. But update statement (in plain SQL world) can be used for few joined tables. And as SQL::Abstract::More doesn't support such feature (yet?) I have to use workaround like this: my ( $stmt, @bind ) = $sql -> update( -table => $sql -> join( @join ) -> {sql}, -set => { 't5.onhand' => \ [ 't5.onhand + t4.partQty * ?' => - $qty_diff ], }, -where => \%where, ); If support of such feature wasn't dropped on purpose, can it be added to 'update' statements the way it's done for 'select' statements? At least it would provide syntax consistency. And shouldn't break anything AFAIK. With best regards, Val

Mon Nov 24 12:15:23 2014 valkoles [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #99760] AutoReply: join tables in update
Date:	Mon, 24 Nov 2014 12:15:13 -0500
To:	bug-SQL-Abstract-More [...] rt.cpan.org
From:	val <valkoles [...] gmail.com>

Looks like with the following changes --- /usr/local/perl/5.16.1/lib/site_perl/5.16.1/SQL/Abstract/More.pm_orig 2014-11-24 11:32:30.195779870 -0500 +++ /usr/local/perl/5.16.1/lib/site_perl/5.16.1/SQL/Abstract/More.pm 2014-11-24 11:53:34.461748359 -0500 @@ -134,7 +134,7 @@ -returning => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, ); my %params_for_update = ( - -table => {type => SCALAR}, + -table => {type => SCALAR|ARRAYREF}, -set => {type => HASHREF}, -where => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, -order_by => {type => SCALAR|ARRAYREF|HASHREF, optional => 1}, @@ -384,6 +384,15 @@ my %args; if (&_called_with_named_args) { %args = validate(@_, \%params_for_update); + + # compute join info if the update target is a join + if (ref $args{-table} eq 'ARRAY' && $args{-table}[0] eq '-join') { + my @join_args = @{$args{-table}}; + shift @join_args; # drop initial '-join' + my $join_info = $self->join(@join_args); + $args{-table} = \($join_info->{sql}); + } + @old_API_args = @args{qw/-table -set -where/}; } else { the following constructions produce the same output: my @join = ( 'inventory_stock|t1' ); my ( $stmt, @bind ); ( $stmt, @bind ) = $sql -> update( -table => $sql -> join( @join, qw/ t1.id=idSku part_map|t4 idPart=id part|t5 / ) -> {sql}, -set => { 't5.onhand' => \ [ 't5.onhand + t4.partQty * ?' => - $qty_diff ], }, -where => { %where, 't4.partQty' => { '>', 0 }, }, ); print "$stmt, @bind\n\n"; ( $stmt, @bind ) = $sql -> update( -table => [ -join => ( @join, qw/ t1.id=idSku part_map|t4 idPart=id part|t5 / ), ], -set => { 't5.onhand' => \ [ 't5.onhand + t4.partQty * ?' => - $qty_diff ], }, -where => { %where, 't4.partQty' => { '>', 0 }, }, ); Diff was based on 'select' processing, though 'select' has special processing for $join_info related to bind values, I'm not sure that applies to 'update'. Also documentation might be altered to mention 'join' processing if suggested change is valid.

Tue Nov 25 13:13:44 2014 valkoles [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #99760] AutoReply: join tables in update
Date:	Tue, 25 Nov 2014 13:13:28 -0500
To:	bug-SQL-Abstract-More [...] rt.cpan.org
From:	val <valkoles [...] gmail.com>

Did a little research. Suggested fix is for MariaDB/MySQL only. Other SQL implementation use different approaches.

Sun Oct 29 03:29:41 2017 DAMI [...] cpan.org - Status changed from 'new' to 'resolved'

Sun Oct 29 03:29:41 2017 DAMI [...] cpan.org - Fixed in 1.29 added