英文:
postgresql How to SELECT and UPDATE parent and child records from the same table?
问题
你好，我理解你的问题。你遇到的问题似乎是在更新所有具有子记录的父记录时，更新操作未能按预期进行。这里有一些可能导致此问题的原因：
-
数据完整性问题：首先，请确保你的数据表中没有任何外键或约束，它们可能会妨碍更新操作。如果有外键关系，你需要考虑解除它们，然后再次尝试更新。
-
索引问题：检查数据库表是否有适当的索引。确保
parent_load_record_id列和id列都有索引，这将提高查询性能。如果没有索引，更新操作可能会变得非常慢。 -
事务问题：如果你的数据库在一个事务中执行这些更新操作，确保事务正确提交。如果没有正确提交，更新操作将不会生效。
-
缓存问题：有时数据库系统可能会缓存查询结果，这可能导致在数据库中进行的更改不会立即反映在查询中。你可以尝试刷新数据库缓存或等待一段时间，然后再次运行查询。
如果在检查了上述问题后，问题仍然存在，你可以考虑使用数据库日志来跟踪更新操作，以便更好地理解问题发生的原因。此外，确保数据库系统本身没有任何限制，可能会限制大规模更新操作的执行。
最后，确保你在执行更新操作之前备份了数据，以防发生意外情况。如果问题仍然存在，并且你需要更具体的帮助，建议咨询你的数据库管理员或数据库开发人员以获取进一步的支持。
英文:
Hello I have the following records parents and children, you can tell by the parent_load_record_id column.
The thing is I need those records who have children and their children to have false in the is_unique field, since they are not unique anymore.
The parents who have no children have to stay as is_unique = true
| id | document_number | document_status | is_unique | parent_load_record_id |
|---|---|---|---|---|
| 276983 | 49515 | INFORMED | true | |
| 1060396 | 251013 | INFORMED | true | |
| 177836 | 0000221 | INFORMED | true | |
| 207775 | 0000221 | APPROVED | true | 177836 |
| 19342 | 0000221 | RESULTANT | true | 177836 |
| 19721 | 000456 | INFORMED | true | |
| 19786 | 000456 | EDITED | true | 19721 |
The problem I'm having is. I'm trying to get all the parents with this query and I always get zero results (this query I use to check if my update actually worked)
SELECT
l.id,
l.document_number,
l.document_status,
l.is_unique,
l.parent_load_record_id
FROM load_record l
WHERE l.parent_load_record_id IS NULL
AND l.id NOT IN(SELECT DISTINCT lr.parent_load_record_id FROM load_record lr);
I use this query to update all children
UPDATE load_record SET is_unique = false WHERE parent_load_record_id IS NOT NULL
The problem comes when I have to UPDATE all parents with children
UPDATE load_record SET
is_unique = false
WHERE parent_load_record_id IS NULL
AND id IN(SELECT DISTINCT lr.parent_load_record_id FROM load_record lr)
The Update is not really doing what it needs to do, just like the SELECT is there a reason for it? or is there a better way to do it?
答案1
得分: 0
以下是翻译好的部分：
问题在于SELECT DISTINCT lr.parent_load_record_id FROM load_record lr返回了一个NULL行，导致id NOT IN (…)也返回了NULL（示例）。您可以进行过滤：
... AND l.id NOT IN (SELECT DISTINCT lr.parent_load_record_id FROM load_record lr WHERE lr.parent_load_record_id IS NOT NULL);
或者只是使用WHERE NOT EXISTS子句：
... AND NOT EXISTS (
SELECT *
FROM load_record lr
WHERE lr.parent_load_record_id = l.id
)
英文:
The problem is that SELECT DISTINCT lr.parent_load_record_id FROM load_record lr returns a NULL row, which leads to id NOT IN (…) returning NULL as well (demo). You can either filter them:
… AND l.id NOT IN (SELECT DISTINCT lr.parent_load_record_id FROM load_record lr WHERE lr.parent_load_record_id IS NOT NULL);
<sup>(updated demo)</sup>
or just use a WHERE NOT EXISTS clause:
… AND NOT EXISTS (
SELECT *
FROM load_record lr
WHERE lr.parent_load_record_id = l.id
)
<sup>(updated demo)</sup>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论