Can I use REPLACE with a WHERE clause in mysql?

Question

The Title really says it all.

Something like:

REPLACE INTO vips SET active = 0, inactive = 0 WHERE ip = 3494220867 AND proto = "https";

Answer 1

Yes, it works exactly like the more standard insert since it is, after all, a delete/insert equivalent.

See here for details.

---

Based on your added comment:

After I asked this quesiton it occured to me that I'm probably looking for something that provides the same functionality as replace but in an update. I'm trying to update the active and inactive columns if the record exists (if ip and proto exist). If the ip and proto columns don't match a row it will insert it. The query I provided in my original post does not work.

I think you're looking for the INSERT ... ON DUPLICATE KEY UPDATE command.

This will attempt to insert the data and do the update bit if the row already exists.

Now I'm not sure how well this will work unless the ip/proto is the primary key. You may need to revert to the standard method of:

begin transaction.
try:
    insert data into table with default values.
catch exception violates_unique_constraint:
    do nothing.
update data in table with new values.
end transaction