Mysql REPLACE INTO query for Multiple rows insertion

Question

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.

Answer 1

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