如何在循环中更新 SELECT 返回的行? [英] How can I UPDATE rows returned by a SELECT in a loop?

问题描述

我在 SQL SELECT 语句返回的行上有一个循环，并且在对行的数据进行一些处理之后，有时我想更新行的值.循环体中的处理非常重要，我无法用 SQL 编写它.当我尝试为所选行执行 UPDATE 时，出现错误(在 Perl 的 DBD::SQLite::st 下执行失败:数据库表被锁定).是否有一种可读、高效和可移植的方式来实现我想要做的事情?否则，是否有 DBD 或 SQLite 特定的方法来做到这一点?

显然，我可以将更新推送到单独的数据结构中并在循环后执行它们，但我讨厌之后的代码外观.

如果您有兴趣，这里是相应的 Perl 代码.

my $q = $dbh->prepare(q{SELECT id, confLoc FROM Confs WHERE confLocId ISNULL});$q->执行或死亡；我的 $u = $dbh->prepare(q{更新 Confs SET confLocId = ?哪里 id = ?});而(我的 $r = $q->fetchrow_hashref){接下来除非 ($r->{confLoc} =~ m/something-hairy/);除非 ($locId = unique_name_state($1, $2));$u->execute($locId, $r->{id}) 否则死亡；}

解决方案

暂时启用AutoCommit:

<前>sqlite> .header 打开sqlite> select * from test;场一两个

#!/usr/bin/perl使用严格；使用警告；使用 DBI；我的 $dbh = DBI->connect('dbi:SQLite:test.db', undef, undef,{ RaiseError =>1、自动提交=>0});test_select_with_update($dbh);子 test_select_with_update {我的 ($dbh) = @_;本地 $dbh->{AutoCommit} = 1;我的 $q = $dbh->prepare(q{SELECT field FROM test});我的 $u = $dbh->prepare(q{UPDATE test SET field = ? WHERE field = ?});$q->执行或死亡；而(我的 $r = $q->fetchrow_hashref ){如果((我的 $f = $r->{field})eq 'one'){$u->execute('1', $f) 否则死亡；}}}

代码运行后:

<前>sqlite> .header 打开sqlite> select * from test;场1两个

I have a loop on the rows returned by an SQL SELECT statement, and, after some processing on a row's data, I sometimes want to UPDATE the row's value. The processing in the loop's body is non-trivial, and I can't write it in SQL. When I try to execute the UPDATE for the selected row I get an error (under Perl's DBD::SQLite::st execute failed: database table is locked). Is there a readable, efficient, and portable way to achieve what I'm trying to do? Failing that, is there a DBD or SQLite-specific way to do it?

Obviously, I can push the updates in separate data structure and execute them after the loop, but I'd hate the code's look after that.

If you're interested, here is the corresponding Perl code.

my $q = $dbh->prepare(q{
  SELECT id, confLoc FROM Confs WHERE confLocId ISNULL});
$q->execute or die;
my $u = $dbh->prepare(q{
  UPDATE Confs SET confLocId = ? WHERE id = ?});
while (my $r = $q->fetchrow_hashref) {
    next unless ($r->{confLoc} =~ m/something-hairy/);
    next unless ($locId = unique_name_state($1, $2));
    $u->execute($locId, $r->{id}) or die;
}

解决方案

Temporarily enable AutoCommit:

sqlite> .header on
sqlite> select * from test;
field
one
two

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('dbi:SQLite:test.db', undef, undef,
    { RaiseError => 1, AutoCommit => 0}
);

test_select_with_update($dbh);

sub test_select_with_update {
    my ($dbh) = @_;
    local $dbh->{AutoCommit} = 1;
    my $q = $dbh->prepare(q{SELECT field FROM test});
    my $u = $dbh->prepare(q{UPDATE test SET field = ? WHERE field = ?});
    $q->execute or die;
    while ( my $r = $q->fetchrow_hashref ) {
        if ( (my $f = $r->{field}) eq 'one') {
            $u->execute('1', $f) or die;
        }
    }
}

After the code has been run:

sqlite> .header on
sqlite> select * from test;
field
1
two

这篇关于如何在循环中更新 SELECT 返回的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！