如何使用一个查询（perl-mysql）更新多行？

Question

我需要用一个查询更新多行。对于插入，我们通常会执行类似的操作

INSERT INTO `table` (c1,c2,c3) VALUES 
 (1,2,3),
 (4,5,6),
 ..

，但我们如何才能为更新执行类似的操作，其中每一行的值都与另一行不同？而且有一个条件是和每一行的id相关的？

有人遇到过类似的问题吗？

我现在必须如何进行更新的示例是：

UPDATE questions
SET lab='What sections do you believe the site must not have?',
    type='textarea',
    options=''
WHERE rnum=11;

UPDATE questions
SET lab='What is your favourate section?',
    type='radio',
    options='section1,section2,section3,section4,section5'
WHERE rnum=12;

等等。这绝对是最糟糕的方法，因为每个查询都需要执行，而且可能有多达 20 个。

Answer 1

考虑下面的示例，它增加 id > 的列“col_name”的所有值。 5 且 col_name 不为 0

id    col_name
1       1
5       2
6       3
7       5
8       6
9       7
10      0

查询

update tbl_name SET col_name=col_name+1 where id > 5 and col_name !=0

O/P 将是

id    col_name
1       1
5       2
6       4
7       6
8       7
9       8
10      0

Answer 2

如果您要使用另一个表中的值更新一个表，这很容易：

update table1,table2
SET table1.value = table2.value
WHERE table1.key = table2_foreign_key

如果您要通过以一致的方式更改行子集来更新一个表，这很容易：

update table1
SET table1.value = (table1.value * 2)
WHERE table1.id in (SELECT id from table1 where table1.key > 50);

如果您尝试更新一个表中的多条记录，其中每条记录的更新方式都不同，如果不使用第二个表，这基本上是不可能的。最好在您的代码中执行此操作。

Answer 3

随着时间的推移，我找到了一种更好的方法来做到这一点，并且通过一个查询，而不是使用 for 循环。
这个想法是在重复更新上使用插入并将记录键放在插入上，这将强制查询进行更新。

INSERT INTO table_name 
                            (key, a, b) 
                            VALUES 
                            (1,'apple','orange'),
                            (2,'xyz','abc')
                            ON DUPLICATE KEY
                            UPDATE 
                            a= VALUES(a),
                            b= VALUES(b)

Answer 4

使用占位符，您可以通过多次执行同一查询来完成此操作：

my @data = (
  [ 'new_lab1', 'new_type1', 'new_opt1', 1 ],
  [ 'new_lab2', 'new_type2', 'new_opt2', 2 ],
);

my $sql = <<EOT;
UPDATE questions
SET lab=?,
    type=?,
    options=?
WHERE rnum=?
EOT

my $sth = $dbh->prepare($sql);
for my $datum (@data) {
    $sth->execute(@$datum);
}

Answer 5

首先，为什么需要在一个查询中执行此操作？如果您需要以原子方式完成更新，可以将它们包装在 START TRANSACTION; 和 COMMIT; 中吗？这可能就是我会做的。

但是，如果您确实需要在一个查询中完成此操作，并且要更新的行数相当小，那么这是可以完成的！您可能可以使用这个丑陋的小技巧：

UPDATE questions
SET
lab = IF(rnum=11, 'What sections...?',
        IF(rnum=12, 'What is your...?',
          IF(rnum=13, 'Etc.',
            NULL
          )
        )
      ),
type = IF(rnum=11, 'textarea',
         IF(rnum=12, 'radio',
           IF(rnum=13, 'Etc.',
             NULL
           )
         )
       )
WHERE rnum IN (11, 12, 13);

在 perl 中编写循环来构造自然的怪胎，这将作为练习:)

如果您正确编写它并且表如您所相信的那样，那么永远不应该使用那些剩余的 NULL。为了增加安全性，如果可以的话，您可能希望将其中一些列声明为 NOT NULL，这样，如果您的 rnum 不匹配，则赋值将导致约束失败并且UPDATE将被中止。