Oracle SQL：根据另一个表中的值有条件地更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1465291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL: update table conditionally based on values in another table
提问by Walker
[Previous essay-title for question]
[以前的文章标题问题]
Oracle SQL: update parent table column if all child table rows have specific value in a column. Update RANK of only those students who have 100 marks in all the subjects. If student has less than 100 marks in any subject, his RANK should not be updated.
Oracle SQL：如果所有子表行在列中都有特定值，则更新父表列。仅更新所有科目均达到 100 分的学生的 RANK。如果学生在任何科目中的分数低于 100 分，则不应更新其排名。
I have a scenario where I have a parent table and a child table. The child table has a foreign key to parent table. I need to update parent table's status column when a column in child table rows have specific values. There are more than one child records for each parent, in some cases none. Is it possible to achieve this with Oracle SQL, without using PL/SQL. Is that possible, can some one explain how? In some case I have to update parent table row's column based on two columns of child table records.
我有一个场景，我有一个父表和一个子表。子表具有父表的外键。当子表行中的列具有特定值时，我需要更新父表的状态列。每个父级有多个子记录，在某些情况下没有。是否可以使用 Oracle SQL 实现这一点，而不使用 PL/SQL。这可能吗，有人可以解释一下吗？在某些情况下，我必须根据两列子表记录更新父表行的列。
My exact problem is like : I have two tables STUDENTS, MARKS. MARKS has a FK to STUDENTS named STUDENT_ID.MARKS has number of rows for a STUDENT record, depending on different subjects (MARKS has a FK to SUBJECTS), and has a column named MARKS_OBTAINED. I have to check that if MARKS_OBTAINED for one student for every subject (i.e. all his records in MARKS) have value 100, then update STUDENT table's column RANK to a value 'Merit'. This query:
我的确切问题是：我有两个表 STUDENTS、MARKS。MARKS 有一个名为 STUDENT_ID 的 STUDENTS 的 FK。MARKS 有一个 STUDENT 记录的行数，取决于不同的主题（MARKS 有一个 FK 到 SUBJECTS），并且有一个名为 MARKS_OBTAINED 的列。我必须检查每个科目的一名学生的 MARKS_OBTAINED（即他在 MARKS 中的所有记录）的值是否为 100，然后将 STUDENT 表的列 RANK 更新为值“Merit”。这个查询：
update STUDENT
set RANK = 'Merit'
where exists ( select *
from MARKS
where MARKS.STUDENT_ID = STUDENT.ID
and MARKS.MARKS_OBTAINED = 100)
and not exists ( select *
from MARKS
where MARKS.STUDENT_ID = STUDENT.ID
and MARKS.MARKS_OBTAINED != 100)
updates all those student who have 100 marks in any subject. It does not exclude records which have non 100 marks. Because it passes rows for a STUDENT in MARKS where one record in MARKS has 100 MARKS_OBTAINED but other records have less than 100 marks, but since STUDENT obtained 100 marks in one subject, its RANK will also get updated. The requirement is that if any STUDENT records has a MARKS record with non 100 value in MARKS_OBTAINED column this STUDENT record should get excluded from the query.
更新所有在任何科目中获得 100 分的学生。它不排除非 100 分的记录。因为它为 MARKS 中的 STUDENT 传递行，其中 MARKS 中的一条记录具有 100 MARKS_OBTAINED 但其他记录的分数低于 100，但由于 STUDENT 在一个主题中获得了 100 分，因此其 RANK 也会更新。要求是，如果任何 STUDENT 记录在 MARKS_OBTAINED 列中具有非 100 值的 MARKS 记录，则该 STUDENT 记录应从查询中排除。
回答by APC
Total rewrite
完全重写
This is a complete rewrite to fit my example to the OQ's revised question. Unfortunately Manish has not actually run my original solution otherwise they would realise the following assertion is wrong:
这是一个完整的重写，以使我的示例适合 OQ 的修订问题。不幸的是，Manish 实际上并没有运行我原来的解决方案，否则他们会意识到以下断言是错误的：
Your solution returns all those student who have 100 marks in any subject. It does not exclude records which have non 100 marks.
您的解决方案会返回所有在任何科目中获得 100 分的学生。它不排除非 100 分的记录。
Here are six students and their marks.
这是六名学生和他们的分数。
SQL> select * from student
2 /
ID RANK
---------- ----------
1 normal
2 normal
3 normal
4 normal
5 normal
6 normal
6 rows selected.
SQL> select * from marks
2 /
COURSE_ID STUDENT_ID MARK
---------- ---------- ----------
1 1 100
2 1 100
1 2 100
2 2 99
1 4 100
2 5 99
1 6 56
2 6 99
8 rows selected.
SQL>
Student #1 has two courses with marks of 100. Student #4 has just the one course but with with a mark of 100. Student #2 has a mark of 100 in one course but only 99 in the other course they have taken. None of the other students scored 100 in any course. Which students will be awarded a 'merit?
学生 #1 有两门课程，分数为 100。学生 #4 只有一门课程，但分数为 100。学生 #2 在一门课程中获得了 100 分，但在他们选修的另一门课程中只有 99。其他学生在任何课程中都没有获得 100 分。哪些学生将获得“优异成绩”？
SQL> update student s
2 set s.rank = 'merit'
3 where exists ( select null
4 from marks m
5 where m.student_id = s.id
6 and m.mark = 100 )
7 and not exists ( select null
8 from marks m
9 where m.student_id = s.id
10 and m.mark != 100)
11 /
2 rows updated.
SQL>
SQL> select * from student
2 /
ID RANK
---------- ----------
1 merit
2 normal
3 normal
4 merit
5 normal
6 normal
6 rows selected.
SQL>
And lo! Only those students with 100 marks in alltheir courses have been updated. Never underestimate the power of an AND.
还有！只有在所有课程中获得100 分的学生才进行了更新。永远不要低估一个AND.
So the teaching is: an ounce of testing is worth sixteen tons of supposition.
所以教导是：一盎司的测试抵得上十六吨的假设。
回答by Andrzej Doyle
Your question is a little too vague at the moment to really answer fully. What happens to a parent row if it has no children? What happens if some of the child rows have specific values but not all of them? In the two-column case, what combinations of number of children/values are needed (is is the same set of values for each column or unique ones? Is it an AND relationship or an OR relationship)? Etc...
你的问题目前有点太模糊，无法真正完整地回答。如果没有子行，父行会发生什么？如果某些子行具有特定值但不是全部值，会发生什么情况？在两列的情况下，需要哪些子项/值的组合（每列的值是相同的还是唯一的？是 AND 关系还是 OR 关系）？等等...
Anyway, making the assumption that there needs to be at least one child row with a value in a given domain, this should be fairly straightforward:
无论如何，假设在给定域中至少需要一个具有值的子行，这应该相当简单：
update PARENT set STATUS = 'whatever'
where ID in (
select parent_id from CHILD
where value_col in ('your', 'specific', 'values', 'here')
);
This general pattern expands to the multi-column case easily (just add an extra AND or ORed condition to the inner where clause), and to the negative case too (change where ID into where ID not in).
这种通用模式很容易扩展到多列情况（只需在内部 where 子句中添加一个额外的 AND 或 ORed 条件），也可以扩展到否定情况（更改where ID in为where ID not in）。
If performance of this update is an issue you may want to look at triggers - at the price of slightly slower inserts on the child tables, you can keep your parent table up-to-date on an ongoing basis without having to run this update statement periodically. This works quite nicely because the logic of inspecting each child row is essentially distributed across each individual insert or update on the child table. Of course, if those child modifications are performance-critical, or if the child changes many times in between the points where you need to update the parent, then this wouldn't work very well.
如果此更新的性能是一个问题，您可能需要查看触发器 - 以在子表上插入稍慢的代价，您可以持续保持父表最新，而无需运行此更新语句定期。这非常有效，因为检查每个子行的逻辑基本上分布在子表上的每个单独的插入或更新中。当然，如果这些子项修改对性能至关重要，或者如果子项在您需要更新父项的点之间多次更改，那么这不会很好地工作。
回答by Jonathan Leffler
What about:
关于什么：
UPDATE ParentTable
SET StatusColumn = 78
WHERE PK_Column IN
(SELECT DISTINCT FK_Column
FROM ChildTable AS C1
WHERE (SELECT COUNT(*) FROM ChildTable C2
WHERE C1.FK_Column = C2.FK_Column) =
(SELECT COUNT(*) FROM ChildTable C3
WHERE C1.FK_Column = C3.FK_Column
AND C3.OtherColumn = 23)
)
I strongly suspect there are neater ways to do it, but...the correlated sub-queries count the number of rows in the child table for a particular parent and the number of rows in the child table for the same parent where some filter condition matches a particular value. Those FK_Column values are returned to the main UPDATE statement, giving a list of primary key values for which the status should be updated.
我强烈怀疑有更简洁的方法可以做到这一点，但是......相关的子查询计算特定父级的子表中的行数以及某些过滤条件的同一父级的子表中的行数匹配特定值。这些 FK_Column 值返回到主 UPDATE 语句，给出状态应该更新的主键值列表。
This code enforces the stringent condition 'all matching rows in the child table satisfy the specific condition'. If your condition is simpler, your sub-query can be correspondingly simpler.
此代码强制执行严格条件“子表中的所有匹配行都满足特定条件”。如果您的条件更简单，您的子查询也可以相应地更简单。
