morph to a foreign key

[Zane]

I need some help...
I have a list of around 800 college classes,with the description, course name, the hours, etc
[color=orange]-created from REGEXing the hell out of these straight HTML pages ... btw (ouch)
http://www.southwesterncc.edu/acadprog/desc/aca-bus.htm#aca[/color]


This is the way the courses table looks
[code]mysql> select id,coursenum, name from courses limit 10;
+----+-----------+-------------------------------------+
| id | coursenum | name                                |
+----+-----------+-------------------------------------+
|  1 | aca115    | Success and Study Skills            |
|  2 | aca118    | College Study Skills                |
|  3 | aca120    | Career Assessment                  |
|  4 | aca220    | Professional Transition            |
|  5 | acc115    | College Accounting                  |
|  6 | acc121    | Principles of Managerial Accounting |
|  7 | acc129    | Individual Income Taxes            |
|  8 | acc131    | Federal Income Taxes                |
|  9 | acc150    | Acct Software Appl                  |
| 10 | acc175    | Hotel and Restaurant Accounting    |
+----+-----------+-------------------------------------+[/code]

If you look at the coursenum field you notice it says the major a lot...
[quote]acc
acc
acc
acc
..
..[/quote]

I also have a table full of these things with their keys
[code]mysql> select * from curriculums limit 10;
+----+--------+---------------------+
| id | prefix | name                |
+----+--------+---------------------+
|  1 | aca    | Academic Related    |
|  2 | acc    | Accounting          |
|  3 | ant    | Anthropology        |
|  4 | art    | Art                |
|  5 | aut    | AUTOMOTIVE          |
|  6 | baf    | Banking and Finance |
|  7 | bio    | BIOLOGY            |
|  8 | bpr    | Blueprint Reading  |
|  9 | bus    | Busniess            |
| 10 | car    | Carpentry          |
+----+--------+---------------------+[/code]

maybe someone's caught on by now, but I can't figure how I would do this;

I want to create new field in courses.....easy
go through every record's coursenum field...add the corresponding curriculum id to the new field leaving just the numbers.....WHAT how..

I'm clueless here....
anyone have any advice or suggestions...I know I could make a PHP script to do this, with an array
but that would take quite a bit of processing power

My goal is to get it to look like this
[code]
+----+------------+-----------+-------------------------------------+
| id | curriculum | coursenum | name                                |
+----+------------+-----------+-------------------------------------+
|  1 |          1|    115    | Success and Study Skills            |
|  2 |          1|    118    | College Study Skills                |
|  3 |          1|    120    | Career Assessment                  |
|  4 |          1|    220    | Professional Transition            |
|  5 |          2|    115    | College Accounting                  |
|  6 |          2|    121    | Principles of Managerial Accounting |
|  7 |          2|    129    | Individual Income Taxes            |
|  8 |          2|    131    | Federal Income Taxes                |
|  9 |          2|    150    | Acct Software Appl                  |
| 10 |          2|    175    | Hotel and Restaurant Accounting    |
+----+------------+-----------+-------------------------------------+[/code]

[Zane]

Alright, I use the DUMP feature in phpmyadmin to get a textlist
and used Dreamweaver's regex feature to do close to what I needed..
I have it looking like this now
[code]
+------+-----------+-------------------------------------+
| curr | coursenum | name                                |
+------+-----------+-------------------------------------+
| aca  | 115      | Success and Study Skills            |
| aca  | 118      | College Study Skills                |
| aca  | 120      | Career Assessment                  |
| aca  | 220      | Professional Transition            |
| acc  | 115      | College Accounting                  |
| acc  | 121      | Principles of Managerial Accounting |
| acc  | 129      | Individual Income Taxes            |
| acc  | 131      | Federal Income Taxes                |
| acc  | 150      | Acct Software Appl                  |
| acc  | 175      | Hotel and Restaurant Accounting    |
| acc  | 193      | Selected Topics-Forensic Accounting |
| aca  | 215      | Ethics in Accounting                |
| acc  | 220      | Intermediate Accounting I          |
| acc  | 221      | Intermediate Accounting II          |
| acc  | 225      | Cost Accounting                    |
| acc  | 269      | Audit and Assurance Services        |
| acc  | 292      | Selected Topics in Accounting      |
| ant  | 210      | General Anthropology                |
| ant  | 220      | Cultural Anthropology              |
| ant  | 221      | Comparative Cultures                |
+------+-----------+-------------------------------------+
[/code]

Still trying to figure out to convert them to their respective indeces
any thoughts

[Zane]

um.....bump

[fenway]

Easiest way would be to add a curr_id column, then issue a multi-table UPDATE where you pull the ID associated with the name and put in into this new column, and then drop the old varchar column.

[Zane]

I hate asking for code but what would that look like
I knew I'd have to use a combination of UPDATE and SELECT but I couldn't get the syntax right

when I first tried it..I got the error
that there was more than one row returned.
a query like this
UPDATE course2 a SET curr = (SELECT b.id from curriculums b where b.prefix = a.curr)

[fenway]

Not a problem... I was thinking of something like:

[code]
UPDATE courses AS co LEFT JOIN curriculums AS cu ON ( co.curr = cu.prefix ) SET co.currnum = cu.id
[/code]

[Zane]

Yes....
genious....it worked perfect
thanks

question....if I had used inner join, right join, or just join
would it had made any difference

[fenway]

No one should ever use right join; just join = inner join, and if you did that, any non-matches wouldn't be set to NULL explicitly.