开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜