Mysql REPLACE not working

Question

For some reason my script isn't deleting existing rows when I'm using replace.

I'm working on an inventory management system, and I may be calling this script every hour or so to update the db:

   REPLACE INTO inventory(username, sku,asin,set_price,inventory)
                   VALUES('trav','AEG5502','B00875JE0C','23.49','');

but instead of deleting the old row, it creates a new row with the exact same info.

I'm sure it's a simple error, but I would love if someone could help me out.

Additional info, a row has about 15 columns. None of these columns (username, sku,asin,set_price,inventory) are a primary key, I don't know if that has to be the case for replace to work, but I thought I'd mention it.

Thanks

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. — hank
– hank, Commented Mar 20, 2013 at 6:44
Offtopic. But I see you insert a username into the row. It's wiser to include a user_id instead of a name. What if the username changes? — Bart
– Bart, Commented Mar 20, 2013 at 6:46
@bart: assuming proper foreign key setups, you can cascade such a change. — Marc B
– Marc B, Commented Mar 20, 2013 at 13:54

Marc B · Accepted Answer · 2013-03-20 06:44:13Z

4

RTLM: http://dev.mysql.com/doc/refman/5.0/en/replace.html replace will only replace if ANY of the fields you're using are the primary or at least a unique key in the table. since none of your fields are, it simply does an insert

answered Mar 20, 2013 at 6:44

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1556695 Over a year ago

Shoot, I guess I didn't think about that when I setup the database. Thanks for the help though! Is there any way to simulate a sku being unique if username = trav? I'm guessing that some users will have the same sku numbers.

Marc B Over a year ago

offhand guess: slap a unique index on the (username,sku) tuple?

Collectives™ on Stack Overflow

Mysql REPLACE not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related