[SOLVED] copy id from one database to another database

[mikeabe]

Hello

 

I'm trying to get the student id from one database to another database.

 

Database#1 has every student's name only once.

 

Database#2 could have any student's name several times.

 

 

Database structure:

Student

    studnames

          sid        (student id)

          snames  (student names)

 

disciplineincoming

    incoming

          sid          (student id)

          student  ( student names)

         

 

This code updates some id's but not others:

UPDATE disciplineincoming.incoming A
INNER JOIN student.studnames B
ON A.Student = B.snames
SET A.sid = B.sid

 

 

Is there a way to change the code so that it updates every id in Database#2?

 

 

Thanks, Mike

[rhodesa]

what are you trying to do exactly?

[premiso]

That code should work, what you have to worry about is someone with the same name, such as "John Smith", this will set every student with the name "john smith" to the last id done.

 

Look at the ones that are not updating, check that there is not an extra space or something in the name. If you use phpMyAdmin, maybe do a select on both the databases using the logic you have above and see what pulls out and why.

[mikeabe]

Premiso

the student names are in this format:

 

Becera Juan 8

Martinez Maria 6

 

The number at the end of their name signifies what grade they are in (from 6-

if there happens to be two Martinez Maria 6 in the sixth grade, their student number is added to their name. So all the names are different.

 

Right now, there are 701 rows of data in the main database.

The code in the original post worked on approximately 1/4 of those names, (giving them an id in the main database.)

 

It's not obvious to me how it chooses the names it updates. The updated ones range from A to Z. The ones that are updated are all given the correct id.

 

Multiple instances of the same name are SOMETIMES all given an id, sometimes not.

 

 

If I run the code a second time, it says 0 rows were affected.

 

I've looked very closely at the names in Mysql and there are no extra spaces or obvious errors.

 

The teachers select the names from a drop down menu form, no typing involved, so no typing errors.

 

 

 

 

Aaron

I'm trying to post the student id from one database to another one.  The "bad" or absent kids names go to the second database. I'll use their id in the second database to group and track them. (absents, tardies, etc). (by the way, we also use the database to record the good things kids do. It's really a handy tool for our campus)

 

 

This is the last piece of the project. I really appreciate both of your help.

[mikeabe]

Although the code shown in the first post doesn't seem to update mysql database very well, it does seem to work well with new names being posted, and I can live with that. I just hand edited the previous postings (938 entries)

Thanks guys for your help, I appreciate it.

 

Mike