[SOLVED] update with replace, Possible?

[asmith]

Hi,

 

I use something like this to update some fields:

 

$result = mysql_query("update table set visit = visit+1 where ...

if mysql_affected_rows() == 0 
mysql_query("insert into ...

 

Is it possible to write such thing by replace? for example:

 

mysql_query("replace into table (id, visit) values (id, visit+1)")
or
mysql_query("replace into table (id, visit) values (id, ifnull(visit, 0) + 1)")
or
mysql_query("replace into table (id, visit) values (id, ifnull((select visit from table where id='theNumber'), 0) + 1)")

 

first 2 does not give any error but they simply put only 1.  It doesn't add 1 to the existing value.

the third gives me

You can't specify target table 'table' for update in FROM clause

 

I'm just curious about this. No reason why I wanna change to this way.

 

 

 

[gizmola]

Replace is a non-standard MySQL extension that has nothing to do with UPDATE really.  What it's really focused on is INSERT.  It was added to allow people to bulk insert rows into a table, and giving you a tool to deal with problems caused by key violations that might otherwise cause the bulk insert to fail.  In essence what the REPLACE does is:

 

-Does primary key of INSERT == Primary key of a row?

--- If YES

------- DELETE ROW

-INSERT NEW ROW

 

So, when you are doing an UPDATE, and specifying a value to be based on the existing value, you get the behavior you've noted, which is, that the original value get "lost" because that row gets Deleted.

 

So in short, Replace really isn't meant to be a substitute for an UPDATE, and due to the nature of the checks involved, is also going to be slower than an Update.  Replace was really added to help people who want to repeatedly re-insert a base set of rows into an existing table.

[asmith]

Thanks for your replay. You made it very clear for me.

 

Thanks a lot

[gizmola]

Glad it helped.

[aschk]

What you can do instead if

INSERT .. ON DUPLICATE KEY UPDATE...

 

e.g.

INSERT INTO table(visit) VALUES(0) ON DUPLICATE KEY UPDATE SET visit = visit+1

[asmith]

wow yea I totally had forgotten that.

 

cool, I'll use it. Its faster than (if mysql_affected_rows() == 0 then insert), Right?