Creating customer_id based on matchcodes in Oracle (SQL)

Question

I have an oracle database containing purchases of customers. (one record is one purchase) Customers provided their personal data again and again at every purchase. So there can be differences due to mistype, address change, etc. Now I have to identify purchases belonging to the same customer.

To do that I created 3 different match code based on simple rules. My table looks somehow like this now:

  
+-------------+-------------+-------------+-------------+-------------+
| PURCACHE_ID | MATCHCODE_1 | MATCHCODE_2 | MATCHCODE_3 | CUSTOMER_ID |
+-------------+-------------+-------------+-------------+-------------+
|             |             |             |             |             |
| 1           | 1           | b           | x           |             |
|             |             |             |             |             |
| 2           | 1           | a           | y           |             |
|             |             |             |             |             |
| 3           | 2           | c           | x           |             |
|             |             |             |             |             |
| 4           | 3           | a           | z           |             |
|             |             |             |             |             |
| ...         | ...         | ...         | ...         | ...         |
+-------------+-------------+-------------+-------------+-------------+

What I want to do is to assign a customer_id to every purchase. Same customer_id would be assigned to purchases where any matchcode equals to another one.

So for example purchase 1 and purchase 2 would receive the same customer_id because matchcode 1 is the same. Also purchase 2 and purchase 4 belong to the same customer cause Matchcode_2 is the same. Thereby even purchase 1 and purchase 4 would receive the same customer_id though none of their matchcodes equals.

Customer_id can be a simple number starting from 1.

What is the SQL code to make Customer_Id?

Answer 1

A naive solution:

-- Just number them
UPDATE purchases SET customer_id = rownum;

-- Group all customers with given matchcode_1 into one
MERGE INTO purchases p
USING (SELECT matchcode_1, min(customer_id) customer_id
         FROM purchases
    GROUP BY matchcode_1) m
ON (p.matchcode_1 = m.matchcode_1)
WHEN MATCHED THEN
  UPDATE SET p.customer_id = m.customer_id;

-- Repeat the above merge for matchcode_2, matchcode_3
-- then matchcode_1 again and so on
-- until none of the matchcodes make any updates

You could write something nicer with PL/SQL probably...