MySQL Forums
Forum List  »  Stored Procedures

Re: In Stored Procedure INSERT INTO SELECT or CURSOR
Posted by: Peter Brawley
Date: December 04, 2015 04:37PM

Quote

INSERT INTO tmp_table
SELECT ID FROM TABLE3
UPDATE i, t, s
SET
t.name1 = s.name1,
t.name2 = s.name2,
t.name3 = s.name3,
t.name4 = s.name4
WHERE
i.id = t.i_id AND i.id in (SELECT ID from tmp_table);

First, you can't Insert into one table and Update another in the same statement. The command as shown could not have taken any time at all---it would exit with an error right away. You need a semicolon before the Update command, to make two commands.

Next, your Update comma join of i,t,s has no Where clause for table s, so it cross-joins s to the join of i and t. Surely not what you had in mind.

And, ...i.id in (SELECT ID from tmp_table)... will perform poorly, because MySQL optimises In(Select...) badly; that should become a simple inner join.

And as your errors show, comma joins look easier to write but are much harder to analyse and debug. Use explicit join syntax.

So your update query needs to be something like ...

UPDATE i
join t         on i.id = t.i_id 
join s         on i.id = s.i_id -- or whatever the table s joining col is
join tmp_table on i.id=tmp_table.id
SET t.name1 = s.name1, 
    t.name2 = s.name2, 
    t.name3 = s.name3, 
    t.name4 = s.name4 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: In Stored Procedure INSERT INTO SELECT or CURSOR
1193
December 04, 2015 04:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.