List:General Discussion« Previous MessageNext Message »
From:Igor Shevtsov Date:July 9 2011 4:13pm
Subject:Re: stored procedure insert statement
View as plain text  
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" <nixofortune@stripped
>> <mailto:nixofortune@stripped>> 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=1
>>

Thread
stored procedure insert statementIgor Shevtsov9 Jul
  • Re: stored procedure insert statementJohnny Withers9 Jul
    • Re: stored procedure insert statementClaudio Nanni9 Jul
    • Re: stored procedure insert statementIgor Shevtsov9 Jul