append on insert 2007-11-03 - By Baron Schwartz
Back
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 (See http://tem-2.ora-code.com)
> or even my-item-123 (See http://tem-123.ora-code.com) 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/mysql?unsub=mysql@(protected)
|
|
