Hi all.

I have a table which looks like this:

CREATE TABLE table_1 (
column_1 INTEGER AUTO_INCREMENT PRIMARY KEY,
column_2 TEXT NOT NULL
);

INSERT INTO table_1 (column_2) VALUES ('hehe');

column_2 needs to be TEXT (not VARCHAR) and thus I cannot use PRIMARY
KEY or UNIQUE there. This is a problem because I do not want to have
duplicates in column_2.

ON DUPLICATE UPDATE KEY doesn't seem to work. Is there a workaround
here?

Thanks.

On Tue, 25 Dec 2007 13:57:55 -0800 (PST), Mikhail Kovalev
<mikhail_kovalev@mail.ru> wrote:

>Hi all.
>
>I have a table which looks like this:
>
>CREATE TABLE table_1 (
> column_1 INTEGER AUTO_INCREMENT PRIMARY KEY,
> column_2 TEXT NOT NULL
>);
>
>INSERT INTO table_1 (column_2) VALUES ('hehe');
>
>column_2 needs to be TEXT (not VARCHAR) and thus I cannot use PRIMARY
>KEY or UNIQUE there. This is a problem because I do not want to have
>duplicates in column_2.
>
>ON DUPLICATE UPDATE KEY doesn't seem to work. Is there a workaround
>here?

Use a hash on your TEXT column as a key:

CREATE TABLE table_1 (
column_1 INTEGER AUTO_INCREMENT PRIMARY KEY,
column_2 TEXT NOT NULL,
key_2 CHAR(32) UNIQUE
);

INSERT INTO table_1 (column_2,key_2)
VALUES ('hehe',MD5('hehe'));

>Thanks.

HTH
--
( Kees
)
c[_] Preudhomme's Law of Window Cleaning: It's on the other side. (#512)

On 25 Des, 22:57, Mikhail Kovalev <mikhail_kova...@mail.ru> wrote:
> Hi all.
>
> I have a table which looks like this:
>
> CREATE TABLE table_1 (
> * * column_1 INTEGER AUTO_INCREMENT PRIMARY KEY,
> * * column_2 TEXT NOT NULL
> );
>
> INSERT INTO table_1 (column_2) VALUES ('hehe');
>
> column_2 needs to be TEXT (not VARCHAR) and thus I cannot use PRIMARY
> KEY or UNIQUE there. This is a problem because I do not want to have
> duplicates in column_2.
>
> ON DUPLICATE UPDATE KEY doesn't seem to work. Is there a workaround
> here?
>
> Thanks.

Btw, how is a duplicate defined in MySQL? Do all the columns have to
have the same value? Or just the primary key?

On Tue, 25 Dec 2007 13:57:55 -0800 (PST), Mikhail Kovalev wrote:
> Hi all.
>
> I have a table which looks like this:
>
> CREATE TABLE table_1 (
> column_1 INTEGER AUTO_INCREMENT PRIMARY KEY,
> column_2 TEXT NOT NULL
> );
>
> INSERT INTO table_1 (column_2) VALUES ('hehe');
>
> column_2 needs to be TEXT (not VARCHAR) and thus I cannot use PRIMARY
> KEY or UNIQUE there. This is a problem because I do not want to have
> duplicates in column_2.
>
> ON DUPLICATE UPDATE KEY doesn't seem to work. Is there a workaround
> here?

Do you mean "ON DUPLICATE KEY UPDATE"? Because that does the opposite of
what it sounds like you want: If there's a duplicate key (column_1),
then change the value of column_2 to the incoming data instead of
throwing out a "duplicate key" error and skipping the row.

It sounds like you want duplicates in column_2 flagged and can't
because the whole column data may not be indexed and thus checked for
duplication. This is something that will have to be at least partly
handled at the application level, but you can do a large chunk of it via
MySQL. One thing (semi-automatic) would be to also store in column_3 a
hash of the value in column_2, perhaps using the MD5() function, and the
unique index built over column_3 instead of column_2. If rolled into a
Stored Procedure and that used instead of an insert, very little code
would remain outside of MySQL.



--
"Friendship is born at that moment when one person says to another, 'What!
You too? I thought I was the only one!'"
--C.S. Lewis

>> I have a table which looks like this:
>>
>> CREATE TABLE table_1 (
>> * * column_1 INTEGER AUTO_INCREMENT PRIMARY KEY,
>> * * column_2 TEXT NOT NULL
>> );
>>
>> INSERT INTO table_1 (column_2) VALUES ('hehe');
>>
>> column_2 needs to be TEXT (not VARCHAR) and thus I cannot use PRIMARY
>> KEY or UNIQUE there. This is a problem because I do not want to have
>> duplicates in column_2.
>>
>> ON DUPLICATE UPDATE KEY doesn't seem to work. Is there a workaround
>> here?

It's "ON DUPLICATE KEY UPDATE". Note: DUPLICATE KEY.

>Btw, how is a duplicate defined in MySQL? Do all the columns have to
>have the same value? Or just the primary key?

If inserting the record causes a "duplicate key error", the update
happens instead. (If you just did a straight insert of the same
record, the insert would fail.) A record is a duplicate if all the
columns in any PRIMARY KEY or UNIQUE INDEX match that of an existing
record. You can have more than one such index, so a duplicate in
any one of the indexes counts as a duplicate.

Example: fields: A, B, C, D, and E
PRIMARY KEY: A
UNIQUE INDEX: B
UNIQUE INDEX: C,D

If the inserted record's A field matches an existing record, it's a duplicate.
If the inserted record's B field matches an existing record, it's a duplicate.
If the inserted record's C and D fields both match the same existing
record, it's a duplicate.

On 28 Des, 00:57, gordonb.sm...@burditt.org (Gordon Burditt) wrote:
> >> I have a table which looks like this:
>
> >> CREATE TABLE table_1 (
> >> * * column_1 INTEGER AUTO_INCREMENT PRIMARY KEY,
> >> * * column_2 TEXT NOT NULL
> >> );
>
> >> INSERT INTO table_1 (column_2) VALUES ('hehe');
>
> >> column_2 needs to be TEXT (not VARCHAR) and thus I cannot use PRIMARY
> >> KEY or UNIQUE there. This is a problem because I do not want to have
> >> duplicates in column_2.
>
> >> ON DUPLICATE UPDATE KEY doesn't seem to work. Is there a workaround
> >> here?
>
> It's "ON DUPLICATE KEY UPDATE". *Note: DUPLICATE KEY.
>
> >Btw, how is a duplicate defined in MySQL? Do all the columns have to
> >have the same value? Or just the primary key?
>
> If inserting the record causes a "duplicate key error", the update
> happens instead. *(If you just did a straight insert of the same
> record, the insert would fail.) *A record is a duplicate if all the
> columns in any PRIMARY KEY or UNIQUE INDEX match that of an existing
> record. *You can have more than one such index, so a duplicate in
> any one of the indexes counts as a duplicate.
>
> Example: *fields: A, B, C, D, and E
> PRIMARY KEY: A
> UNIQUE INDEX: B
> UNIQUE INDEX: C,D
>
> If the inserted record's A field matches an existing record, it's a duplicate.
> If the inserted record's B field matches an existing record, it's a duplicate.
> If the inserted record's C and D fields both match the same existing
> record, it's a duplicate.

Ok, thanks.

The only reason I use PRIMARY KEY in the first column is for automatic
increment, which doesn't seem to work without being PRIMARY. I could
use max(column_1) + 1 on insert and use md5 hash as key, but I don't
know how it perform speed wise, especially as the number of columns
grows...

>>>> ON DUPLICATE UPDATE KEY doesn't seem to work. Is there a workaround
>>>> here?
>> It's "ON DUPLICATE KEY UPDATE". Note: DUPLICATE KEY.


Looking at the subject: if there is nothing to update, but you really
want to insert only if something does not exist, you can use the INSERT
IGNORE syntax also.

Regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/