replace into

[drifter]

I was looking at the "replace into" and I am not sure I get this...

I have a table

id (auto increment primary key)
user
item (I can make a user/item index that is unique)
morefields
anotherfield

I want to do a replace into. If I do
replace into mytable set user='joe', item='shirt', morefields='red'

will this update the row if it exists or create a new one if it doesn't for jack/shirt? Do I not need to know the primary key?

Thanks
Scott

[btherl]

The documentation is here: http://dev.mysql.com/doc/refman/5.1/en/replace.html

From that page

[quote] MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):

  1.Try to insert the new row into the table
  2.While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
        1.Delete from the table the conflicting row that has the duplicate key value
        2.Try again to insert the new row into the table[/quote]

That would indicate that a unique index on the values you want to be unique will have the effect you want, ie it will force replacement rather than creating a new row.

[drifter]

thanks for the clarification