At 17:54 +0300 9/19/02, Savaidis wrote:
>I had already done the first (insert ignore into bla-bla ) solution with
>personal e-mail from a friend. It works fast and fine. But the word "ignore"
>leds me to missunderstanding: insert the row inspite of the unique key!
That's why it's a good idea to read the manual if you're not sure about
what something means.
>"continue" I think it is more meanfull as without this I get an error
>message and the job stops at this row.
A matter of taste, perhaps. I don't find IGNORE confusing, and I don't
find CONTINUE meaningful, myself.
>I think also something like "explain" to insert should exist as to select
>too (and to other commands too). We could locate missplaced quots , missing
>fields ecc to run the whole .sql without actualy insert it to the table.
>Also the error message couldn't be more precise? I mean to show where is the
>problem on the insert line? Close too -> shows always the begin of the line.
EXPLAIN cannot work in the way you describe, because it requires a
syntactically legal statement to begin with. Syntax errors are the domain
of the parser. If it says the error is close to the beginning of the line,
normally that's exactly what is wrong.
>Something relative: If the table contains dublicate keys I haven't define
>yet, I should export to .sql , empty the table, define the keys and import
>again or is there a simpler way to delete dups at once?
Yes. ALTER IGNORE TABLE, as stated below.
>Also is possible to MERGE identical tables with same unique keys and abord
If you mean that you want to set up a MERGE table, then you can use SELECT
DISTINCT when you run a query on the table if you want to remove duplicates
from the query result.
You might also try UNION, if you have MySQL 4.x. UNION removes duplicates
by default, unless you say UNION ALL to get all rows including duplicates.
>Or must use export-import trick again?
>JOIN does this or is used only for SELECT?
>From: Paul DuBois [mailto:paul@stripped]
>Sent: Thursday, September 19, 2002 5:10 PM
>To: Savaidis; mysql list
>Subject: Re: insert and deletind dublicating
>At 12:06 +0300 9/19/02, Savaidis wrote:
>>I have to add a .sql file from MySQL-Front
>>(insert into mytable ('0','asdf',ecc))
>>that containts some dublicates in the unic key.
>That sounds self-contradictory.
>>How can I do this and later to delere the dublicate records?
>>The unic key has 3 string fields segments.
>You can either:
>- Create the table, including the unique index, and then load it
> with either INSERT IGNORE or REPLACE
>- Create the table, but do not place any index on it yet. Then load
> then table. Then add the index using ALTER IGNORE TABLE ... rather
> than ALTER TABLE ... to tell it to ignore (and delete) duplicate
> records when creating the index.