CODEWITHSUNDEEP
mysql
Timothy Victor
Timothy Victor

Reputation: 140

On insert increment value of one column of unique composite key if duplicate

I'm transferring data from db to another. The original table has the following structure (where key contact is boolean):

+------------+-----------+-------------+
| contact_id | school_id | key_contact |
+------------+-----------+-------------+
| 1          | 1         | 1           |
| 2          | 2         | 0           |
| 3          | 2         | 1           |
| 4          | 2         | 1           |
| 5          | 3         | 0           |
+------------+-----------+-------------+

I'd like to get it into the following shape where display_order is a composite unique index with school_id: 

+------------+-----------+---------------+
| contact_id | school_id | display_order |
+------------+-----------+---------------+
| 1          | 1         | 1             |
| 2          | 2         | 3             |
| 3          | 2         | 1             |
| 4          | 2         | 2             |
| 5          | 3         | 1             |
+------------+-----------+---------------+

And the logic is if key_contact = 1 then display_order should be 1, unless a contact_id with the same school_id already has a display_order with the value of 1, then it should insert the next incrementally available value that would be unique to the school_id. I'd then like to insert all contacts where key_contact = 0 assigning the next available key_contact integer unique to the school_id.

I've created a unique index for the two columns: 

UNIQUE KEY `contact_school_display_order_school_id_unique` (`display_order`,`school_id`)

I've tried using INSERT... ON DUPLICATE KEY UPDATE but I really want something that will, on duplicate key, increment the value of the insert value to the next integer that is unique to the school_id.

Any pointers would be greatly appreciated.

Upvotes: 0

Views: 52

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

Migrate from table contacts1 to contacts2

insert into contacts2
    select 
        c1.contact_id,
        c1.school_id,
        count(c2.contact_id) as display_order
    from contacts1 c1
    join contacts1 c2
        on  c2.school_id = c1.school_id
        and (
            c2.key_contact > c1.key_contact
            or
            c2.key_contact = c1.key_contact and c2.contact_id <= c1.contact_id
        )
    group by c1.contact_id, c1.school_id
    order by c1.contact_id;

sqlfiddle demo

Explanation

The key logic is in the join condition:

            c2.key_contact > c1.key_contact
            or
            c2.key_contact = c1.key_contact and c2.contact_id <= c1.contact_id

This condition defines that the row from c2 has a higher or equal contact priority for the given school compared to the row from c1. (The priority is only equal if the contact_id is the same.) In the SELECT part we count all the rows with a higher or equal priority for each contact_id from c1 (group by c1.contact_id).

            c2.key_contact > c1.key_contact

is only true if c1.key_contact=0 and c2.key_contact=1. So every row from c2 found by this condition has a higher priority and will be counted.

            c2.key_contact = c1.key_contact and c2.contact_id <= c1.contact_id

If key_contact is equal then we only count the rows with a smaller or equal contact_id.

This gives you the same result as if you would sort your table like this:

select *
from contacts1 c1
order by c1.school_id, c1.key_contact desc, c1.contact_id

and then enumerate the rows for each school.

Upvotes: 1

Related Questions