> >SELECT ( SELECT cards_type.record_num FROM cards_type WHERE
> >cards_type.card_name LIKE ( substr( trader_haves_old.card_name, 1, 9 ) ||
> >'%' )), trader_haves_old.total_have, ( SELECT logins.record_num FROM logins
> >WHERE logins.name = trader_haves_old.trader ), trader_haves_old.available
> >from trader_haves_old;
> Chris, why do you want to match on only the first nine characters of card_name?
I'm doing that because of the kinds of problems that crop up when you don't
use normalization - the values in the card_name field in the 2 seperate tables
don't always match up. Sometimes there is, say (as an analogy), a first and
last name and sometimes there is only a first name. So by grabbing the first
9 characters of the column for this transfer from the old to the new table, I'll
be able to get the proper record number (from cards_type) for 99+% of the
cards.
Sadly, the MySQL database wasn't normalized and I've been running into a
lot of roadblocks (mainly from the fact that I'm still learning PG) in converting
it over.
> A typical normalization job looks like:
Right. If all the names matched.
> INSERT INTO trader_haves(card_id, total_have, trader_id, available)
> SELECT c.record_num, o.total_have, l.record_num, o.available
> FROM trader_haves_old o
> INNER JOIN cards_type c ON c.card_name = o.card_name
> INNER JOIN logins l ON l.name = o.trader;
I'll try (a slight modified version of) this when I get home.
Thanks!
Chris