0

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

semiColon
  • 205
  • 6
  • 24

2 Answers2

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.

symcbean
  • 47,736
  • 6
  • 59
  • 94
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 *

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45