Mysql REPLACE INTO query for Multiple rows insertion

I'm trying to add multiple rows using 'replace into' , but its updating one row.

REPLACE INTO user_balance
(user_id,acc_type,currency,balance,enable_deposit,
enable_withdrawal,pending_balance,update_ip)  
VALUES  (NEW.id,1,'USD',0,1,0,0,NEW.ip_address),
(NEW.id,1,'GBP',0,1,0,0,NEW.ip_address),
(NEW.id,1,'EUR',0,1,0,0,NEW.ip_address),
(NEW.id,1,'BTC',0,1,0,0,NEW.ip_address);

Is this right, the way am doing it?

PS: am employing a trigger to update the balance table whenever an user is created in the users table. It works well with if I replace one row, for multiple rows, it updates only the last values.

You should avoid using triggers in conjunction with REPLACE INTO on multiple rows. Why ?

REPLACE INTO is nothing more than a mechanical DELETE and INSERT. It can incite mysqld to address deadlocks (See my answer to How I prevent deadlock occurrence in my application?)

Here are two comments that shows the LIFO approach to processing triggers


Posted by Atif Ghaffar on September 23 2007 9:12pm [Delete] [Edit]
PLEASE Note that the REPLACE does a DELETE operation.


We did not realize this and had the triggers that should be triggered
on DELETE triggered.


After checking all the code, we just found a script that does a
replace to refresh the values of some fields.


We should have had used "insert into ... on duplicate update" syntax
instead.


Posted by J Mike on May 4 2009 11:06pm [Delete] [Edit] If you are
using REPLACE INTO... triggers are fired in this order (if delete of
duplcate key is used):

• before insert

• before delete

• after delete

• after insert

Your problem ? An UPDATE trigger will never fire off if this has to be done multiple times in a single operation. At least, I don't see that happening.

SUGGESTIONS

• You may want to use INSERT ... ON DUPLICATE KEY UPDATE (See my answer to Update one table from another table while sorting that table based on one column)

• Stick to doing REPLACE on one row at a time

StackExchange Database Administrators Q#103064, answer score: 4