时间戳

首页 > 解决方案 > 在 Postgres 中使用自引用更新表会更新比预期更多的条目

sql - 在 Postgres 中使用自引用更新表会更新比预期更多的条目

问题描述

我有一张表，其中的问题引用了关于父问题的同一张表。

对列的所有行条目执行 SELECT 查询会将title所有这些问题显示为下面的查询 1/结果 1 对。

查询一：

SELECT t_child.title AS t_child_title, 
    t_child.question AS t_child_question, 
    t_child.parent_qid AS t_child_parent_qid, 
    t_child.language AS t_child_language, 
    t_parent.title AS t_parent_title, 
    t_parent.qid AS t_parent_qid, 
    t_parent.language as t_parent_language
FROM lime_questions AS t_child JOIN lime_questions AS t_parent
ON t_child.parent_qid = t_parent.qid AND t_child.language = t_parent.language
WHERE t_child.title = 'SacroCoccix';

结果1：

 t_child_title |   t_child_question    | t_child_parent_qid | t_child_language | t_parent_title | t_parent_qid | t_parent_language 
---------------+-----------------------+--------------------+------------------+----------------+--------------+-------------------
 SacroCoccix   | Sacro e/ou Cóccix     |               1095 | pt-BR            | lisCortAt      |         1095 | pt-BR
 SacroCoccix   | Sacrum and/or coccyx  |               1095 | en               | lisCortAt      |         1095 | en
 SacroCoccix   | Sacrum and/ or coccyx |               1078 | en               | lisFxAt        |         1078 | en
 SacroCoccix   | Sacro e/ou Cóccix     |               1078 | pt-BR            | lisFxAt        |         1078 | pt-BR
 SacroCoccix   | Sacro e/ou Cóccix     |               1056 | pt-BR            | lisCortPr      |         1056 | pt-BR
 SacroCoccix   | Sacrum and/or coccyx  |               1056 | en               | lisCortPr      |         1056 | en
 SacroCoccix   | Sacro e/ou cóccix     |                973 | pt-BR            | lisFxPr        |          973 | pt-BR
 SacroCoccix   | Sacrum and/or coccyx  |                973 | en               | lisFxPr        |          973 | en

将过滤器添加t_parent = 'lisFxPr'到查询中，结果仅限于问题父标题lisFxPr，如下面的 Query 2/Result 2 对。

查询 2：

SELECT t_child.title AS t_child_title, 
    t_child.question AS t_child_question, 
    t_child.parent_qid AS t_child_parent_qid, 
    t_child.language AS t_child_language, 
    t_parent.title AS t_parent_title, 
    t_parent.qid AS t_parent_qid, 
    t_parent.language as t_parent_language
FROM lime_questions AS t_child JOIN lime_questions AS t_parent
ON t_child.parent_qid = t_parent.qid AND t_child.language = t_parent.language
WHERE t_child.title = 'SacroCoccix' AND t_parent.title = 'lisFxPr';

结果 2：

 t_child_title |   t_child_question   | t_child_parent_qid | t_child_language | t_parent_title | t_parent_qid | t_parent_language 
---------------+----------------------+--------------------+------------------+----------------+--------------+-------------------
 SacroCoccix   | Sacro e/ou cóccix    |                973 | pt-BR            | lisFxPr        |          973 | pt-BR
 SacroCoccix   | Sacrum and/or coccyx |                973 | en               | lisFxPr        |          973 | en

我只想更新Result 2中显示的 2 行。

我正在运行以下查询：

UPDATE lime_questions t_main SET title = 'SacrumCoccyx'
FROM lime_questions AS t_child 
JOIN lime_questions AS t_parent
ON t_parent.qid = t_child.parent_qid AND t_parent.language = t_child.language
WHERE t_main.title = 'SacroCoccix' AND t_parent.title = 'lisFxPr';

但是，这个 UDPATE 查询会更新Result 1中显示的所有 8 个条目。

请问我缺少什么？

标签： sqlpostgresqlsql-update

解决方案

更新语法：

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

你可以试试这个查询。

UPDATE lime_questions AS t_main 
SET title = 'SacrumCoccyx'
FROM lime_questions AS t_parent
WHERE t_main.title = 'SacroCoccix' AND 
t_parent.title = 'lisFxPr' AND 
t_parent.qid = t_main.parent_qid AND 
t_parent.language = t_main.language;

推荐阅读