UPDATE IF DUPLICATE INSERT? w autoincrement PK on INSERT?

Question

Given two tables:

`Students`
id         int, primary, auto-increment
sid        int
fname      varchar
lname      varchar


`Registration`
id          int, primary, auto-increment
student_sid int
coursenum   int

Is there a way to populate a new column in Students with the coursenum of each of the classes they registered for? So every time a second coursenum was added for a given sid, students.id would auto-increment, remaining the primary key, while a new row with the sid, fname, lname and new coursenum would be added to the table.

It's kind of like a backwards version of INSERT...IF DUPLICATE UPDATE. I want UPDATE...IF DUPLICATE INSERT.

Answer 1

Perhaps you can do something like this (see notes below):

    $result = mysql_query("SELECT DISTINCT student_sid from Registration");
while (list($studentID) = mysql_fetch_row($result))
{
    $result2 = mysql_query("SELECT fname, lname FROM Students WHERE sid = '{$studentID}'");
    list($fname, $lname) = mysql_fetch_row($result2);

    mysql_query("DELETE FROM Students WHERE sid = '{$studentID}'");
    mysql_query("INSERT INTO Students (sid, fname, lname, coursenum) SELECT '{$studentID}', '{$fname}', '{$lname}', coursenum FROM Registration WHERE student_sid = '{$studentID}'");
}

A few notes here:

1. This code is not tested, but I think it should work without errors.
2. This will not preserve the original id column from the Students table. It will get the first and last name, then delete the student, and insert a new row for each entry in the Registration table. It will preserve the sid, fname, and lname, and will populate a new column coursenum.
3. This also assumes that you have already altered the Students table to include a coursenum column.
4. Depending on how many students and courses you're talking about here, this code may not be the fastest in the world, but I'm assuming that this isn't something that will be done regularly...more of a one-time migration kind of thing.

Hopefully this helps you.