Thanks Johnny, In this case I wouldn't be able to insert a completely new row but replace the existent one, so row count would stay the same. This is a storage table with the only unique constraints on: dda_debits_id column. the test data is very small, so I would've noticed any duplicates and they wouldn't make it to the table anyway with or without INSERT IGNORE.
+-----------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-------------+------+-----+---------+----------------+ | dda_debits_id | int(11) | NO | PRI | 0 | | | created_on | datetime | YES | | NULL | | | reference_number | varchar(18) | YES | | NULL | | | user_format_debit_ref | varchar(18) | YES | | NULL | | | amount | int(11) | YES | | NULL | | | debit_date | datetime | YES | | NULL | | | status | tinyint(1) | YES | | NULL | | | debit_type | tinyint(1) | YES | | NULL | | | recharge_for_id | int(11) | YES | | NULL | | | processed_on | datetime | YES | | NULL | | | service_user_id | int(11) | YES | | NULL | | +-----------------------+-------------+------+-----+---------+----------------+ Claudio, good point. Unfortunately, didn't work. I tried it before but no luck. Thanks, Igor On 07/09/2011 02:43 PM, Johnny Withers wrote: > > It seems to me that your insert statement is trying to insert > duplicate rows into the storage table. This is why insert ignore and > replace work. > >> On Jul 9, 2011 3:49 AM, "Igor Shevtsov" <nixofort...@googlemail.com >> <mailto:nixofort...@googlemail.com>> wrote: >> >> Hi all, >> I can't explain strange behaviour of the INSERT statement in the stored >> procedure. >> The idea is to generate a list based on the output of 3 INNER JOIN of >> regularly updated tables. >> Something like : >> >> INSERT INTO storage >> (column list) >> SELECT >> column list >> FROM t1 JOIN t2 >> ON t1.x=t2.y >> JOIN t3 >> ON t2.z=t3.w >> WHERE CONDITIONS; >> >> The procedure runs daily by crontask and it inserts correct number of >> output rows. >> >> But after It runs and populated a storage table, I added new entries and >> expect to find them in the storage table. Even though they were picked >> up by SELECT statement, they haven't been INSERTed into the storage >> table. >> If I DELETE or TRUNCATE from the storage table and run the procedure all >> newly added entries and existed entries are their, but if I add new rows >> and run the procedure again It doesn't update the table. >> All tables have a unique identifier, so duplicate errors are impossible. >> I use INNODB engine for all tables. >> I understand that stored procedure is a precompiled thing and I believe >> it could be something to do with cache but I couldn't find proper >> explanation or similar case online. >> I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive >> the proper result with newly entries added to the storage table. >> Any ideas guys? >> Have a nice weekend ALL. >> Cheers, >> Igor >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=joh...@pixelated.net >>
