SQL Query to count entire duplicate row

Question

I am looking for help with a query to do the following:

Before the insert of a row, find how many rows in 2 tables have the same information as is being inserted. So basically I am looking to see if this row will be a complete duplicate. I want to base this on all the columns, not just the PK, because if there is even one column different then this is a valid insert.

This is something along the lines of what I need, although incorrect:

SELECT COUNT(*)
FROM ORDER_TRF_HEADER
WHERE
((SELECT * FROM ORDER_TRF_HEADER_COMPLETE WHERE MA_PONUM = '29608207') = (SELECT * FROM ORDER_TRF_HEADER WHERE MA_PONUM = '29608207'));

Table - ORDER_TRF_HEADER

MA_CUST VARCHAR2(8 BYTE) MA_PONUM VARCHAR2(30 BYTE) MA_ODATE VARCHAR2(8 BYTE) MA_ITEMS NUMBER(3,0) MA_SALEM VARCHAR2(2 BYTE) MA_PDAYS NUMBER(3,0) MA_CURR VARCHAR2(3 BYTE)

Table - ORDER_TRF_HEADER_COMPLETE

MA_CUST VARCHAR2(8 BYTE) MA_PONUM VARCHAR2(30 BYTE) MA_ODATE VARCHAR2(8 BYTE) MA_ITEMS NUMBER(3,0) MA_SALEM VARCHAR2(2 BYTE) MA_PDAYS NUMBER(3,0) MA_CURR VARCHAR2(3 BYTE)

Thanks

To me, this sounds like a bad way to do things, but think about declaring a unique constraint on "all the columns minus the primary key". — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Nov 6, 2017 at 17:32

symcbean · Accepted Answer · 2017-11-06 17:22:58Z

1

I want to base this on all the columns, not just the PK, because if there is even one column different then this is a valid insert.

then your issue is that you have NOT defined your primary key correctly.

Certainly there are good reasons for not maintaining a primary key consisting of every attribute in the record, however a better solution than checking for duplicates in such a clumsy way before inserting would be to maintain a has of the data as a unique key.

answered Nov 6, 2017 at 17:22

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kaushik Nayak · Accepted Answer · 2017-11-06 17:43:22Z

0

You can try INTERSECT.

SELECT COUNT(*)
FROM   (SELECT *
        FROM   order_trf_header_complete
        WHERE  ma_ponum = '29608207'
        INTERSECT
        SELECT *
        FROM   order_trf_header
        WHERE  ma_ponum = '29608207') ;

Note:: You better use all column names explicitly instead of select *

edited Nov 6, 2017 at 17:43

answered Nov 6, 2017 at 17:36

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

SQL Query to count entire duplicate row

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related