List:General Discussion« Previous MessageNext Message »
From:Igor Shevtsov Date:July 9 2011 8:52am
Subject:stored procedure insert statement
View as plain text  
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
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