on duplicate key update help

I've tried to use the following but I get an error near 'WHERE'. Is this possible or do I need to use something else?

If the vendor does not exist, insert a new row. If the vendor does exist then update the row where the item_id is equal to som开发者_高级运维ething.

INSERT INTO vendor_item VALUES(1,1)
ON duplicate KEY UPDATE vendor_id=1
WHERE item_id=3

The INSERT statement has no WHERE clause. You can only update the row that is the duplicate of what you wanted to insert so it makes no sense to have a condition.

If you want more complex actions MySQL has triggers available. But maybe it would be better to not do it entirely in MySQL.

The way ON DUPLICATE KEY UPDATE works is as follows. Say you have a table ("tableA") w/3 fields in it (fieldA, fieldB, fieldC) and let's pretend that fieldA is set to be unique. Let's also pretend that there is already 1 row of data in that table: fieldA fieldB fieldC 1 'foo' 'test'

Considering the following query:

INSERT into tableA (fieldA,fieldB,fieldC) VALUES (1,'bar','example') ON DUPLICATE KEY UPDATE fieldB=VALUES(fieldB),fieldC=VALUES(fieldC)

MySQL will assume that fieldA is unique and the new records will become: fieldA fieldB fieldC 1 'bar' 'example'

As w/anything else, I'd recommend reading up on it before using it in production:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html