sql - 删除表 PostgreSQL 中的重复条目

Question

如果用户喜欢另一个用户，我尝试创建一个 pg/plsql 脚本来删除 TABLE 中的双重条目并在另一行中报告，这是代码：

\timing on
DO $$
BEGIN
    DECLARE
        i INT := 1;
        maxID INT;
    BEGIN
        CREATE TEMP TABLE query ("id" SERIAL, "idUser1" INT, "idUser2" INT, "favorite" BOOLEAN);
        
        INSERT INTO query FROM "WU_MatchingUsers" WHERE "id" IN (SELECT "id" FROM (SELECT "id", ROW_NUMBER() OVER( PARTITION BY "IDWU_User1", "IDWU_User2" ORDER BY "id" DESC) AS row_num FROM "WU_MatchingUsers") t WHERE t.row_num >1), "UserAFavoriteTag"; 
        
        SELECT INTO maxID MAX("id") FROM query;
        
        WHILE i < maxID LOOP
            IF SELECT "favorite" FROM query WHERE "id" = i; THEN
                SELECT * FROM "WU_MatchingUsers" WHERE "id" IN (SELECT "id" FROM (SELECT "id", ROW_NUMBER() OVER( PARTITION BY "IDWU_User1", "IDWU_User2" ORDER BY "id" DESC) AS row_num FROM "WU_MatchingUsers") t WHERE t.row_num >1) WHERE "IDWU_User1" = SELECT "idUser1" FROM query AND "IDWU_User2" = SELECT "idUser2" FROM query;
                ELSE
                    RAISE NOTICE "Not IF";
            END IF;
        DELETE FROM "WU_MatchingUsers" WHERE "id" IN (SELECT "id" FROM (SELECT "id", ROW_NUMBER() OVER( PARTITION BY "IDWU_User1", "IDWU_User2" ORDER BY "id" DESC) AS row_num FROM "WU_MatchingUsers") t WHERE t.row_num >1), "UserAFavoriteTag"; 
    END;
END $$;
\timing off

这是 psql 返回的错误：

psql:duplicateDeleter.sql:22: ERREUR:  erreur de syntaxe sur ou près de « FROM »
LIGNE 9 :         INSERT INTO query FROM "WU_MatchingUsers" WHERE "id"...

标签： sqlpostgresqlduplicates