Can I use REPLACE with a WHERE clause in mysql?
Something like:
REPLACE INTO vips SET active = 0, inactive = 0 WHERE ip = 3494220867 AND proto = "https开发者_开发问答";
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
精彩评论