Re: How can I do this?

On Thu, 9 Jan 2003 07:08:15 -0600, "Chris Boget" <chris(at)wild(dot)net>
wrote:
>> 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.

So if you have

card_id | card_name
--------+--------------
277 | 123456789abc
727 | 123456789bbc
772 | 123456789bca

which card_id do you want to select for trader_haves_old.card_name =
'123456789bbc'?

>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.

While you are still unfamiliar with the new DB, don't make several
steps at once. Strictly separate the tasks of
a) importing data,
b) cleaning up structures (normalization),
c) cleaning up data.

You already have done (a), because there is a table trader_haves_old.
If you do step (b) like suggested, you end up with data that are not
worse than now. You can always look for similar names later:

SELECT count(*), substring(card_name, 1, 9)
FROM cards_type
GROUP BY substring(card_name, 1, 9)
HAVING count(*) > 1;

... and then eliminate unwanted card_names one by one (if there are
only a few):

SELECT card_id, card_name
FROM cards_type
WHERE card_name LIKE '123456789%';

UPDATE trader_haves
SET card_id = 277
WHERE card_id IN (727, 772);

DELETE FROM cards_type
WHERE card_id IN (727, 772);

Good luck!
Servus
Manfred