Kevin Waterson wrote:
I have a table of item
I wish to have the value of the item incremented if there is a duplicate.
I looked at ON DUPLICATE KEY UPDATE but this modifies the exsisting value.
If the item is my-item and this already exists, I need to make it my-item-2
or even my-item-123 where 123 is the key.
For the record, what you're doing sounds like an abuse of databases, but...
INSERT INTO item (`key`)
SELECT CONCAT('my-item',
(SELECT IF(COUNT(*) = 0, '', CONCAT('-', COUNT(*)))
FROM item WHERE `key` LIKE 'my-item%'));
Before anyone points out the problems with this, of which there are
several, remember I'm only giving Kevin enough rope to hang himself ;-)
Kevin, this design is not first normal form and will cause you trouble.
Consider what will happen if you insert my-item, my-item, and then my-ite.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
