0

Need some assistance in this query. I was doing 3 queries to perform what i just found out can be done with 1. But it keeps adding a new record instead of updating..

INSERT INTO vendors (vendor, name, tel, email, popularity) VALUES
('ibm','support','911','ibm@ibm.com','0')  ON DUPLICATE KEY UPDATE
vendor='ibm', name = 'support', tel = '911',email = 'ibm@ibm.com',
popularity = popularity+1

I have the 'id' column set as unique... Schema below.

CREATE TABLE `vendors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vendor` text NOT NULL,
`name` text NOT NULL,
`tel` text NOT NULL,
`email` text NOT NULL,
`popularity` int(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

This is new for me but i have checked the syntax on the mysql site.. Can't see the problem.

Thanks

Leo505
  • 109
  • 1
  • 13
  • you syntax is probably perfect. – Drew Oct 04 '16 at 16:58
  • 1
    You have ID as unique, but you're not inserting the ID. Figure out what other column you want to be unique (email, perhaps?) and set that to unique as well. – aynber Oct 04 '16 at 16:58
  • dupe target, focus on the word `clash` – Drew Oct 04 '16 at 16:59
  • When creating the table, modify this Unique Key(`id`,`email`) – Ricardo González Oct 04 '16 at 17:08
  • I would suggest NOT using ID to check for dupes, instead use `email` and `tel` eg; things that don't normally change frequently Create a 2 column unique index on those 2 columns and it should work as expected. – Duane Lortie Oct 04 '16 at 17:15
  • Erm... The ID is auto-incremented so i did not have this as insert... Does the query itself need to contain a column that is a key? To cause the 'clash'??? – Leo505 Oct 04 '16 at 17:18

0 Answers0