We used UNION operator to combine the two select command queries, and now we want delete duplicates using with CTE. Will UNION operator works with CTE ?
Loading
We used UNION operator to combine the two select command queries, and now we want delete duplicates using with CTE. Will UNION operator works with CTE ?
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Janarthanan SPosted May 25, 2023, 1:26 PM
Yes, you can use the UNION operator with a common table expression (CTE) to eliminate duplicates in your query results. The CTE is essentially a temporary result set that you can reference within your query.
Here's an example of how you can use the UNION operator with a CTE to delete duplicates:
WITH cte AS (
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2
)
DELETE FROM cte
WHERE EXISTS (
SELECT *
FROM cte c
WHERE c.column1 = cte.column1
AND c.column2 = cte.column2
-- Add additional columns for comparison if needed
AND c.ctid < cte.ctid -- This keeps the first occurrence and deletes the duplicates
);