Manual SQL Merge Organizations - AppStateESS/clubconnect GitHub Wiki

Oh no, you were dealing with something for Terri and noticed that you have two different organization_ids for clubs with names that are either similar or identical or for some other reason you know they are the same thing (ie, ALUG and OSUM)

How to Merge Organization Records

Note, in this document, look for '...' in the SQL to see where you need to put something. It should be pretty clear what goes there.

Look up to see that they basically have the same name (please never rely on equivalence because it's often an errant space or other typo):

SELECT * FROM sdr_organization_recent WHERE name ILIKE '%...%';
-- Here you want to specify a word or two in the name to try and catch
-- as many potential duplicates as possible, usually it's only two but 
-- I've seen three

If in doubt, now is when you call Terri. Equestrian Team and Equestrian Club are NOT the same thing, for instance, but everything gets bad quickly if you just assume they are.

Now you have a couple or more IDs...

SELECT * FROM sdr_organization_instance WHERE organization_id IN (...) ORDER BY term;

From SDR's perspective, we are moving memberships from a newer club into an older club and deleting the newer club. You have to think of it like that. So you have a club to keep and a club to delete.

Now you have to pick which organization_id to keep. You pretty much always want to keep the older, smaller ID. It may be the case that the newer ID has a banner_id set - in this case, look at the sdr_organization table to set that banner_id on the older club, like so:

UPDATE sdr_organization SET banner_id='...' where id=...;

First thing you need to do is create new instances for the club you are going to keep:

INSERT INTO sdr_organization_instance SELECT
    nextval('sdr_organization_instance_seq'),
    ...,  -- organization_id that you are KEEPING
    term, name, type, address, bank, ein, shortname
FROM sdr_organization_instance WHERE organization_id=...; -- organization_id that you are DELETING

Now look at the output from the sdr_organization_instance query. In most cases, the you won't have two instances in the same term, and this is the easier case to deal with. Basically you can fix the memberships with one query. But if the club has two or more instances for a term, your only option really is to manually merge the membership records. Now, some clubs have hundreds of members, and usually what happens is even though there are two registrations, one gets most of the people for some reason. Maybe it's first in the list, I don't know. So the way to think of this is, make sure all of the membership records exist in one of the clubs and then change the organization_id. Here's how to get a pretty clear comparison of memberships between two clubs:

SELECT * FROM sdr_membership WHERE organization_id IN (...) -- both the old and the new go here
    ORDER BY term, member_id;

Look for two lines in a row with the same member_id and same term. Delete one of them, doesn't matter which, but you can be smart about which one has more membership records.

Now that you have the proper instance record created and a unique set of memberships for the club instance, you need to delete the incorrect instance record. Foreign keys will stop you from shooting yourself in the foot here, but if you follow the guide below you shouldn't have any trouble:

-- Fix Memberships
UPDATE sdr_membership SET organization_id=... -- the ID to keep
    WHERE organization_id=...; -- the ID to delete

Note: If for some reason you find yourself in a situation where you need to delete a membership record and re-create it, DO NOT FORGET to check sdr_membership_role for the membership_id you are about to delete. I THINK foreign keys will protect you here but just know that UPDATE sdr_membership you don't need to worry about sdr_membership_role, but if for whatever reason you end up with a new membership_id for a record, you need to double check sdr_membership_role before you delete the old record. I have no fancy tricks for this, you just have to deal with it on a case by case basis.

-- Fix registrations (lol)
UPDATE sdr_organization_registration SET organization_id=... -- the ID to keep
    WHERE organization_id=...; -- the ID to delete

-- Fix officer requests
UPDATE sdr_officer_request SET organization_id=... -- the ID to keep
    WHERE organization_id=...; -- the ID to delete

-- Fix profiles
-- Note: I was a bit naive in the design of this table, and it is possible
-- for a club to have two profiles, in which case SDR just takes the first
-- (probably wrong) one.  Before updating, make sure the ID to KEEP does NOT
-- have a profile record:
DELETE FROM sdr_organization_profile WHERE organization_id=...; -- the ID to delete
UPDATE sdr_organization_profile SET organization_id=... -- the ID to keep
    WHERE organization_id=... -- the ID to delete

At this point, there should be no foreign keys stopping you from deleting the sdr_organization_instance record:

DELETE FROM sdr_organization_instance WHERE organization_id=...

And then you can delete the sdr_organization record:

DELETE FROM sdr_organization WHERE id=...

And they're merged!