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" <[email protected]>
To: "DBIx::Class user and developer list" <[email protected]>
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/[email protected]
_______________________________________________
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/[email protected]
