mysql - PL/SQL rownum 更新

Question

我正在处理一个包含几个表的数据库。他们是一个

      districts table
      PK district_id

      student_data table
      PK study_id
      FK district_id

      ga_data table
      PK study_id
      district_id

ga_data表是我加入的数据。student_data表和ga_data表都有130万条记录。 study_id 在两个表之间是 1 到 1，但是 ga_data.district_id 是 NULL，需要更新。我在使用以下 PL/SQL 时遇到问题:

update ga_data
set district_id = (select district_id from student_data
where student_data.study_id = ga_data.study_id)
where ga_data.district_id is null and rownum < 100;

我需要逐步完成，所以这就是我需要 rownum 的原因。但我是否正确使用它？多次运行查询后，它只更新了 130 万条记录中的大约 8,000 条记录(应该是大约 110 万条更新，因为一些 district_ids 在 student_data 中为空)。谢谢!

Answer 1

ROWNUM 只是在前 n 行之后截断查询。您在 STUDENT_DATA 中有一些行的 DISTRICT_ID 为 NULL。因此，在多次运行之后，您的查询很可能会陷入困境，返回相同的 100 个 QA_DATA 记录，所有这些记录都与那些讨厌的 STUDENT_DATA 行之一匹配。

因此，您需要某种机制来确保您在 QA_DATA 表中按自己的方式逐步工作。标志列是一种解决方案。对查询进行分区以使其命中一组不同的 STUDENT_ID 是另一回事。

不清楚为什么必须以 100 个为一组执行此操作，但最简单的方法可能是使用 BULK PROCESSING(至少在 Oracle 中:此 PL/SQL 语法在 MySQL 中不起作用)。

这里是一些测试数据:

SQL> select district_id, count(*)
  2  from student_data
  3  group by district_id
  4  /

DISTRICT_ID   COUNT(*)
----------- ----------
   7369        192
   7499        190
   7521        192
   7566        190
   7654        192
   7698        191
   7782        191
   7788        191
   7839        191
   7844        192
   7876        191
   7900        192
   7902        191
   7934        192
   8060        190
   8061        193
   8083        190
   8084        193
   8085        190
   8100        193
   8101        190
               183

22 rows selected.

SQL> select district_id, count(*)
  2  from qa_data
  3  group by district_id
  4  /

DISTRICT_ID   COUNT(*)
----------- ----------
                  4200

SQL>

这个匿名 block 使用批量处理 LIMIT 子句将结果集批量分成 100 行的 block 。

SQL> declare
  2      type qa_nt is table of qa_data%rowtype;
  3      qa_recs qa_nt;
  4
  5      cursor c_qa is
  6          select qa.student_id
  7                 , s.district_id
  8          from qa_data qa
  9                  join student_data s
 10                      on (s.student_id = qa.student_id);
 11  begin
 12      open c_qa;
 13
 14      loop
 15          fetch c_qa bulk collect into qa_recs limit 100;
 16          exit when qa_recs.count() = 0;
 17
 18          for i in qa_recs.first()..qa_recs.last()
 19          loop
 20              update qa_data qt
 21                  set qt.district_id = qa_recs(i).district_id
 22                  where qt.student_id = qa_recs(i).student_id;
 23          end loop;
 24
 25      end loop;
 26  end;
 27  /

PL/SQL procedure successfully completed.

SQL>

请注意，此构造允许我们在发布更新之前对选定的行进行额外处理。如果我们需要以编程方式应用复杂的修复，这会很方便。

如您所见，QA_DATA 中的数据现在与 STUDENT_DATA 中的数据相匹配

SQL> select district_id, count(*)
  2  from qa_data
  3  group by district_id
  4  /

DISTRICT_ID   COUNT(*)
----------- ----------
   7369        192
   7499        190
   7521        192
   7566        190
   7654        192
   7698        191
   7782        191
   7788        191
   7839        191
   7844        192
   7876        191
   7900        192
   7902        191
   7934        192
   8060        190
   8061        193
   8083        190
   8084        193
   8085        190
   8100        193
   8101        190
               183

22 rows selected.

SQL>