代码之家 › 专栏 › 技术社区 › Alan Wayne

删除交易中的CTE无法删除重复记录

common-table-expression postgresql database

Alan Wayne · 技术社区 · 6 年前

错误:重复的键值违反了唯一约束“doctorbilling\u conference\u unique”

我正在尝试执行以下操作:

-在parent common_procedure表中,我希望将描述从“so is this”更改为“this is a test”。如果导致两个记录具有相同的描述,则这很容易导致common_procedure表中的冲突,因此第一个CTE旨在删除common_procedure表中的所有冲突。

-第二个CTE旨在将引用从(现在已删除的)公共程序记录移动到保留的公共程序记录(良好的记录)。

-最后一次删除的目的是清除“医生账单”表中产生的任何重复项。

失败了! 我所做的每一次尝试都会导致上述错误。似乎语句的delete方面总是出现在插入之前,因此表处于错误状态。

能做到吗?

谢谢你的帮助和建议。

BEGIN;

    SET CONSTRAINTS ALL DEFERRED;

    UPDATE common_procedures cp
    SET description = 'this is a test'          
    WHERE cp.description ='so is this';   


    WITH _t(bad_uid, good_uid) AS (
        DELETE FROM
            common_procedures a
                USING common_procedures b
        WHERE
            a.recid > b.recid             
            AND a.description = b.description
        RETURNING a.uid, b.uid
    ),
    _t1 AS (
        UPDATE doctor_billing
        SET uid = _t.good_uid
        FROM _t
        WHERE uid = _t.bad_uid          
    )
DELETE FROM
        doctor_billing a
            USING doctor_billing b, _t
    WHERE
        a.recid > b.recid             
        AND a.uid = b.uid AND a.encounter_recid = b.encounter_recid AND a.uid = _t.good_uid;

COMMIT;

下面是表定义(大大简化):

CREATE TABLE common_procedures
(
    uid integer NOT NULL,
    description text NOT NULL,
    CONSTRAINT common_procedures_description_key UNIQUE (description)
        DEFERRABLE,
    CONSTRAINT common_procedures_uid UNIQUE (uid)
)

CREATE TABLE doctor_billing
(
    encounter_recid integer NOT NULL,
    uid integer NOT NULL,
    CONSTRAINT doctorbilling_encounter_unique UNIQUE (encounter_recid, uid)
        DEFERRABLE,
    CONSTRAINT doctor_billing_procedure_fk FOREIGN KEY (uid)
        REFERENCES nova.common_procedures (uid) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
        DEFERRABLE
)

1 回复 | 直到 6 年前

Alan Wayne 6 年前

呃。我相信答案就在这里: https://stackoverflow.com/a/13807067/1547335

简言之,使用同一表修改cte的两个数据不应在同一语句中一起使用。这是上面的一个工作版本(使用PetaPoco@)。请注意,不同表上的数据修改是同时进行的,但最终的Delete语句已被分离出来。

BEGIN;
                                            SET CONSTRAINTS ALL DEFERRED;

                                            UPDATE common_procedures cp
                                            SET description =UPPER(TRIM( @0 ))                  
                                            WHERE UPPER(TRIM(cp.description)) = UPPER(TRIM(@1));    

                                            WITH _t(bad_uid, good_uid) AS (
                                                DELETE FROM
                                                    common_procedures a
                                                        USING common_procedures b
                                                WHERE
                                                    a.recid > b.recid            
                                                    AND a.description = b.description
                                                RETURNING a.uid, b.uid
                                            ),
                                             _t2 AS (
                                                UPDATE doctor_billing
                                                SET uid = _t.good_uid
                                                FROM _t
                                                WHERE uid = _t.bad_uid
                                            ),
                                            _t3 AS (
                                                UPDATE nurse_billing
                                                SET uid = _t.good_uid
                                                FROM _t
                                                WHERE uid = _t.bad_uid
                                            )
                                            UPDATE orders
                                            SET uid = _t.good_uid
                                            FROM _t
                                            WHERE uid = _t.bad_uid;

                                        DELETE FROM
                                                doctor_billing a
                                                    USING doctor_billing b
                                            WHERE
                                                a.recid > b.recid             
                                                AND a.uid = b.uid AND a.encounter_recid = b.encounter_recid;

                                        COMMIT;