Mass Update Needed
Moderator: General Moderators
-
curseofthe8ball
- Forum Commoner
- Posts: 73
- Joined: Sun Jun 01, 2003 12:33 am
Mass Update Needed
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?
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?
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
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.
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
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.
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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
-
curseofthe8ball
- Forum Commoner
- Posts: 73
- Joined: Sun Jun 01, 2003 12:33 am
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
-
curseofthe8ball
- Forum Commoner
- Posts: 73
- Joined: Sun Jun 01, 2003 12:33 am
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).
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).
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.');-
curseofthe8ball
- Forum Commoner
- Posts: 73
- Joined: Sun Jun 01, 2003 12:33 am
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.');- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
My apologies. I rushed that query.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.');
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
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!
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!