Merge where definition exists
<p>I have 3 tables I'm working with here. <code>ATTRIBUTE_MAP</code>, <code>GROUP_DEFINITIONS</code>, and <code>GROUP_MAP</code>.</p>

<p><code>ATTRIBUTE_MAP</code> contains the <code>CUST_ID</code> and the associated <code>ATTRIBUTE_ID</code>.</p>

<p><code>GROUP_DEFINITIONS</code> defines a group. Its columns are <code>GROUP_ID</code>, <code>ATTRIBUTE_1</code>, <code>VALUE_1</code>, <code>ATTRIBUTE_2</code>, <code>VALUE_2</code>, <code>ATTRIBUTE_3</code>, <code>VALUE_3</code>  A group consists of 1 to 3 attributes with values.  For example, an attribute could be 'State' with its value being 'New York'.  Values can also be null for boolean values like 'Owns Car'.</p>

<p><code>GROUP_MAP</code> simply maps the <code>CUST_ID</code> to a <code>GROUP_ID</code>.</p>

<p>Now, I'm trying to write a script that will look at the <code>ATTRIBUTE_MAP</code> and see if a customer falls into one of the defined groups in <code>GROUP_DEFINITIONS</code>.  If he (the customer) does, then insert/update a row into <code>GROUP_MAP</code> with the <code>CUST_ID</code> and <code>GROUP_ID</code>.  The part I'm having trouble with, is matching the attribute values.</p>

<p>Here is what I have so far:</p>

<pre><code>merge GROUP_MAP gm using 
( select am.CUST_ID
        ,am.ATTRIBUTE_ID
        ,am.START_DATE
        ,gd.GROUP_ID
        ,gd.ATTRIBUTE_1
        ,gd.VALUE_1
        ,gd.ATTRIBUTE_2
        ,gd.VALUE_2
        ,gd.ATTRIBUTE_3
        ,gd.VALUE_3
from ATTRIBUTE_MAP am, GROUP_DEFINITIONS gd ) src
on gm.GROUP_ID=src.GROUP_ID 
    AND gm.CUST_ID=src.CUST_ID

when not matched then -- create association in GROUP_MAP
insert (CUST_ID, GROUP_ID, FROM_DATE)
values (src.CUST_ID, src.GROUP_ID, src.START_DATE);
</code></pre>

<p>Am I approaching this correctly? I'm guessing I need to just improve my nested select statement in my merge to join the <code>ATTRIBUTE_MAP</code> and <code>PEER_GROUP_DEFINTIONS</code> and then go from there.  Any help/suggestions would be appreciated.</p>

<p>Here's an example for reference:</p>

<p><code>ATTRIBUTE_MAP</code>:</p>

<p><code>+---------+--------------+------------+
| CUST_ID | ATTRIBUTE_ID | VALUE      |
+---------+--------------+------------+
| 50      | 1            | 'New York' |
+---------+--------------+------------+
| 50      | 2            |            |
+---------+--------------+------------+</code></p>

<p><code>GROUP_DEFINITIONS</code>:</p>

<p><code>+----------+-------------+------------+-------------+---------+-------------+---------+
| GROUP_ID | ATTRIBUTE_1 | VALUE_1    | ATTRIBUTE_2 | VALUE_2 | ATTRIBUTE_3 | VALUE_3 |
+----------+-------------+------------+-------------+---------+-------------+---------+
| 10       | 1           | 'New York' | 2           |         |             |         |
+----------+-------------+------------+-------------+---------+-------------+---------+
| 20       | 2           |            |             |         |             |         |
+----------+-------------+------------+-------------+---------+-------------+---------+</code></p>

<p>and so the script should generate (in <code>GROUP_MAP</code>):</p>

<p><code>+---------+----------+--------+
| CUST_ID | GROUP_ID | DATE   |
+---------+----------+--------+
| 50      | 10       | *date* |
+---------+----------+--------+
| 50      | 20       | *date* |
+---------+----------+--------+</code></p>