MySQL Forums
Forum List  »  Newbie

UPDATE TABLE with TRIM() => Duplicate key problems
Posted by: Willem vanSchevikhoven
Date: November 27, 2009 07:06AM

Hey everyone!

I ran into a small problem today. I was cleaning my database where this table exists, its part of an EAV schema.

table_item_properties
item_property_id PK, AI
property_id FK (table_properties)
item_id FK (table_items)
property_value (string)

Which has a unique index on property_id + item_id + property_value.

This table connects properties to items, and the purpose of the unique index is that there are no duplicate properties.

Now lets insert some sample data
INSERT INTO table_item_properties (item_id, property_id, property_value) VALUES
(1, 1, 'Green')
(1, 1, ' Green')

This will insert with no problems. But lets try to trim the values to make things tidy.
UPDATE [IGNORE] table_item_properties SET property_value = TRIM(property_value);

This will cause a duplicate key error. If i use ignore, the update continues and tidies up most of the stuff thats left over from excel or similar typing bugs.

*So my actual question is*
Is there a way to do something like:
UPDATE ... ON DUPLICATE KEY DELETE / MERGE / REPLACE?

Or can someone come up with an other simple solution to the case?

Currently i think i can manage to parse things by hand as there should be like 6 of these cases in the 100000 row tables, but i'd like to solve the problem if it would happen to occur again. I know the way to go is to filter input when the data is inserted, and thats what im going to correct. Just out of curiosity, i'd still like to know if theres a solution for this.

Options: ReplyQuote


Subject
Written By
Posted
UPDATE TABLE with TRIM() => Duplicate key problems
November 27, 2009 07:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.