Mass Update Needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Mass Update Needed

Post by curseofthe8ball »

We have been handed a site by another firm that has several tables in its MySQL database.

One of the tables is a membership table, called 'member_users', which houses member numbers, names, contact information, etc.

A second table is an online reservations table, called 'reservations', which houses the event, number of reservations and some member information, including the member number from member_users which was setup to act as the bridge between the two tables.

The problem that has occured is that the client was unaware of the bridge using the member number and thus they have not been verifiying that the member number is unique.

The client has since created an excel spreadsheet for our use which has unique member numbers for each member in the member_users table, however we need to somehow update over 2000 past reservations in the reservations table to reflect each user's new member number.

Is there any easy way to do this or do we need to do this manually?
User avatar
guitarlvr
Forum Contributor
Posts: 245
Joined: Wed Mar 21, 2007 10:35 pm

Post by guitarlvr »

you could probably do something like get the excel file into a .csv file. then bring each line of the csv file into an array, check to see if first and last name and address equal whats in the database then update the id number.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Is there a way to uniqely id a member other than their member number? I'm assuming there must be, else there would've been a primary key violation already.

If you can figure out a way to uniquely id the members, you should be able to create a table with those primary keys and the new member numbers, then just update the member users table setting the user_number equal to the user number of that member in the other table.
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

ReverendDexter,

The member_users table has a primary key called user_id which is a unique auto_increment number. Updating the member_users table won't be difficult. The problem is updating the reservations table which uses the older membership_numbers from the member_users table to identify the member whom may the reservation. The problem here is that the membership_numbers were not all unique.

The reason we need to update past reservations is because we have been contracted to build a reporting feature that will create reports based on events and members going back to the site's inception. Since we are updating the membership_numbers column in the member_users table to make them all unique, we have to update the membership_number in the reservations table for each reservation so we can build the bridge between the two tables for our reporting features.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

The the member number the ONLY column in the table that can identify the member?
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

superdezign,

There also are First Name and Last Name fields which are stored in the reservations table that are pulled from the member_users table based on the member_number.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

But are the first and last name columns in both tables?
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

Yes. Named first_name and last_name in the member_users table and FirstName and LastName in the reservations table.

The only problem I foresee with using these two fields to help form a bridge is that they are fields that the member can change on their own, so if someone made a reservation a year ago and has since gotten married and changed their last name, then the bridge wouldn't work (but in those cases, we could always do the updating manually, the main focus is to try and get a bulk of the reservations updated as easily and quickly as possible).
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Code: Select all

SELECT CASE WHEN `table1`.`first_name` = `table2`.`FirstName` AND `table1`.`last_name` = `table2`.`LastName` THEN CONCAT('Member #', `table`.`memberid`, ' would be fine.') ELSE CONCAT(`table1`.`first_name`, ' ', `table1`.`last_name`, ' could not be found in both tables.');
That would give you an idea of how many members don't properly exist by name in both tables.
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

What am I doing wrong here?

Code: Select all

SELECT CASE WHEN `member_users`.`first_name` = `reservations`.`FirstName` AND `member_users`.`last_name` = `reservations`.`LastName` THEN CONCAT(`member_users`.`user_id`, ' would be fine.') ELSE CONCAT(`member_users`.`first_name`, `member_users`.`last_name`, ' could not be found in both tables.');
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

curseofthe8ball wrote:What am I doing wrong here?

Code: Select all

SELECT CASE WHEN `member_users`.`first_name` = `reservations`.`FirstName` AND `member_users`.`last_name` = `reservations`.`LastName` THEN CONCAT(`member_users`.`user_id`, ' would be fine.') ELSE CONCAT(`member_users`.`first_name`, `member_users`.`last_name`, ' could not be found in both tables.');
My apologies. I rushed that query.

Code: Select all

SELECT CASE WHEN `member_users`.`first_name` = `reservations`.`FirstName` AND `member_users`.`last_name` = `reservations`.`LastName` THEN CONCAT(`member_users`.`user_id`, ' would be fine.') ELSE CONCAT(`member_users`.`first_name`, `member_users`.`last_name`, ' could not be found in both tables.') END FROM `member_users`, `reservations`;
curseofthe8ball
Forum Commoner
Posts: 73
Joined: Sun Jun 01, 2003 12:33 am

Post by curseofthe8ball »

That query is taking a long time to run. I'll try again later tonight and see what results I get.

As far as how to go about updating the reservations table with the new membership_number, I'm guessing this is going to take a few steps.

1. Add a new column to the reservations table to house the user_id for the member who made the reservation as the user_id is the current unique identifier for the member_users table.

2. Run some kind of UPDATE query that goes through the reservations table, pulling out the MemberNumber, FirstName and LastName, then looking for a match of those three values against the records in the member_users table. If a match is found, grab the user_id of the record in the member_user table and insert it into the reservation table.

3. Update the membership_number in the member_users table for each member (will probably import the new member database from excel).

4. Run another UPDATE query of some kind to pull the user_id column from the reservations table, match it to the user_id of the member_users table and update the MemberNumber column in the reservations table with the value of the membership_number column in the member_users table.

This all sounds like it would work but Im having problems putting together the two UPDATE queries for steps 2 and 4 to complete this task. Any help is greatly appreciated!
Post Reply