[SOLVED] Update of multiple records - efficient way to?

[Conjurer]

I have a database with a number for member records.

 

Names, addresses, phone numbers, etc. 

 

Each record was created with a user_id which was an autogenerated number and is the unique  index for the table.

 

So now I have received a list with updates to some of these records.

 

I basically want to go in to each record based on the user_id and change each column value for the record if the new value is different than the old one.

 

That is pretty easy to do for one record using the Update table set col1=xyz col2=xyz etc where user_id = 1001

 

But with several hundred records to update that is pretty cumbersome having to add the column labels for each row.

 

Is there and easy way to do this?

 

If I were to delete the rows where user_id in (list of user_ids) can I then insert the original user_id with the replacement rows?  Would I need to turn the key off or something in order to do that?

 

Thanks

 

 

[mwasif]

You can update multiple users in a single query if you want same value for all users.

[Conjurer]

Values will all be different.

[mwasif]

Then you have to separetly update all the rows.

[Conjurer]

Maybe I could use Replace.

 

I just found a reference to that.

[fenway]

Values will all be different.

Where are you getting these xyz values?

[Conjurer]

That was just for illustration

 

It is a member directory - so I have existing members where last name might have changed, first name might be different (Robert vs. Bob), lot of addresses and phone numbers have changed.

 

So if it is a member 1004 and the phone number changed I want it to update that record. 

 

Member 1005 maybe got married and needs to change last name.  Member 1006 maybe no changes at all except to mark the 2008 dues as paid, member 1007 changed place of work so the Organization field needs to be changed and the work number needs to change.  etc.

 

 

 

 

[fenway]

Yes, I understand... but where are these values coming from? If could get it into a DB table, you're golden.

[Conjurer]

Coming from an Excel Spreadsheet

 

Which I have had to do a bit of cleanup on - i.e. someone used all caps on everything and I have converted to Proper, etc.

 

[fenway]

Coming from an Excel Spreadsheet

 

Which I have had to do a bit of cleanup on - i.e. someone used all caps on everything and I have converted to Proper, etc.

 

OK, but obviously you can somehow identify which changes are associated with which records?

[Conjurer]

I have an existing directory with about 400 member records in a MySql database

 

The club assistant kept a copy in Excel, and made updates over the last couple years to that one.  So now I am trying to synchronize them, and reformat the club one so I can import it into the existing MySQL database.

 

So in MySQL every  member was assigned an auto created primary key of user_id like 1001, 1002, 1003, etc.

 

I exported the MySQL directory to Excel worksheet.  Then I used a look up where I could to match up the club record with the user_id.  Some in MySQL have expired and not renewed, some in club are totally new.

 

So I added the user_id for the club records that exist in the MySql database.  The assumption now is that if there is a difference in data for a given user_id, that the club record is more current information and the MySQL record should be updated to the new info.

 

So I now have this all set to go but in Excel.  And I am trying to figure out the best way to move it into the MySQL adding the new members and updating any data changes on the existing members.

 

 

Does that make sense>?

[fenway]

Well, assuming you have a new version of mysql, you can use a CSV as a table... that should make it easy to update every column from every user_id found in the club record.

[Conjurer]

So if I did that would I do something like:

 

Create a csv table as newdata

 

and then assuming my existing table is called olddata

 

write an update script something like:

 

Update olddata t1, newdata t2 set t1.first_name = t2.first_name where t1.user_id = t2.user_id

 

 

Where can I find a reference to using the CSV as a table.  Maybe I should just create a new table, insert the new data including the assigned user_id and then do the update like above?

[fenway]

Yes, that's the idea.

 

It's a type of storage engine... see here.

[Conjurer]

Got it sorted

 

Was making it too hard

 

Created a new table called Newdata

 

Then did a

 

Delete from olddata where user_id in (select user_id from newdata)

 

Then did a

 

Insert into olddata select * from newdata