java - 如何更新与同一表的其他记录匹配的表字段

Question

如何更新与同一表的其他记录匹配的表字段。

UPDATE XXXRLES
    SET BIZCAT05 = 'S'
    WHERE ( SELECT A.* FROM XXXRLES A, XXXRLES B
                    WHERE
                        A.BIZFLD = B.BIZFLD
                        AND A.BIZOBJ = B.BIZOBJ
                        AND A.BIZRUL = B.BIZRUL
        );

当且仅当以下 3 个字段的 2 个记录具有相同值时，我需要通过表 XXXRLES 的“S”更新字段 BIZCAT05:

BIZFLD 
BIZOBJ
BIZRUL

---

如何进行查询？

Answer 1

在您在评论中提到 ROWID 后，我假设您的 RDBMS 是 Oracle。

UPDATE A SET
    BIZCAT05 = 'S'
FROM XXXRLES A
JOIN XXXRLES B ON
    A.BIZFLD = B.BIZFLD
    AND A.BIZOBJ = B.BIZOBJ
    AND A.BIZRUL = B.BIZRUL
    AND A.ROWID <> B.ROWID

或使用 exists(最后对我来说看起来更清晰):

UPDATE A SET
    BIZCAT05 = 'S'
FROM XXXRLES A
WHERE EXISTS (SELECT 1 
    FROM XXXRLES B
    WHERE A.BIZFLD = B.BIZFLD
    AND A.BIZOBJ = B.BIZOBJ
    AND A.BIZRUL = B.BIZRUL
    AND A.ROWID <> B.ROWID
)

在评论后添加:

我对 DB2 不熟悉，但你可以试试:

UPDATE XXXRLES A SET
    BIZCAT05 = 'S'
WHERE EXISTS (SELECT 1 
    FROM XXXRLES B
    WHERE A.BIZFLD = B.BIZFLD
    AND A.BIZOBJ = B.BIZOBJ
    AND A.BIZRUL = B.BIZRUL
    AND A.ROWID <> B.ROWID
)