[Dbix-class] Update first X rows

[Yves Räber]

Hi,

* The RDMS is MySQL (5.0.75 with InnoDB storage engine)
* I have a single column primary key (integer, auto increment)
* It definitely supports UPDATE...LIMIT row_count (http://dev.mysql.com/doc/refman/5.0/en/update.html)
* I've already tested it, it work just fine with manual queries

And sorry but the claim in my previous mail is false : if I use the search and update query with a "rows" argument as above, the rows part it is simply ignored.

my $task_to_dispatch = $c->model('myDB::Tasks')->search($sql_args, { rows => $rows });
my $dispatched_tasks = $tasks_to_dispatch->update( { state => 'TODO' } );

If I check the generated query with DBIC_TRACE, the "LIMIT" part is not generated at all, and all the rows are updated.

If there is a more elegant way to do it without writing my own custom SQL query, it would be great.

And by the way I'm using DBIx::Class version 0.08108


----- Original Message -----
From: "Peter Rabbitson" <rabbit+dbic at rabbit.us>
To: "DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
Sent: Tuesday, July 14, 2009 1:29:11 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
Subject: Re: [Dbix-class] Update first X rows

On Tue, Jul 14, 2009 at 09:52:12AM +0200, Yves Räber wrote:
> Hello,
> 
> I need to update the first X rows of a resultset, and I've tried something like this :
> 
> my $to_dispatch = $c->model('myDB::Table')->search($sql_args, { rows => 10 });
> $to_dispatch->update( { state => 'TODO' } );
> 
> It works, but generate a query for each row.
> 
> What I would like to generate is a query that looks like "UPDATE Table SET state = 'TODO' WHERE blablabla LIMIT 10".
> 
> It it possible to do this with DBIx::Class ?

Anything is possible with DBIC, but the real question is - is it possible
with your RDBMS. What I ned to know is:

* Your rdbms type
* What kind of PK is on myDB::Table (single column or multicolumn)
* Are you absolutely sure UPDATE ... LIMIT X actually works, instead of
  LIMIT being simply ignored? Please test with manual queries.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk