MySQL Bugs: #68198: Incorrect usage of UPDATE and ORDER BY

Bug #68198	Incorrect usage of UPDATE and ORDER BY
Submitted:	28 Jan 2013 6:59	Modified:	29 Jan 2013 15:32
Reporter:	Goran Dolenc	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.29	OS:	Windows (8)
Assigned to:		CPU Architecture:	Any
Tags:	1221, 1221, order by, order by, sprroc, sprroc, UPDATE, UPDATE, update union where, update union where

Description:
I have a very simple update statement, which updates a record in one table based on data in another table: 

UPDATE _Brokers AS t1 
JOIN qryBrokers AS t2 ON t1.BrokerID = t2.BrokerID AND t1.BrokerID = 3 
SET 
t1.BrokerName = t2.BrokerName; 

The update runs smoothly. But when placed inside the stored procedure, it runs smoothly only the first time, and the second, third, fourth, etc. time I receive an error: 

"Error Code: 1221. Incorrect usage of UPDATE and ORDER BY" 

What could be the problem?

I have tried 2 other update statements, but the same problem persists.

UPDATE _Brokers, qryBrokers
SET
_Brokers.BrokerName = qryBrokers.BrokerName
WHERE _Brokers.BrokerID = qryBrokers.BrokerID
AND qryBrokers.BrokerID = 3;

UPDATE _Brokers t1 
JOIN qryBrokers t2 USING (BrokerID)
SET    
t1.BrokerName = t2.BrokerName
WHERE
t2.BrokerID = 3;

BTW, qryBrokers is a view, maybe this could be the problem? 

How to repeat:
If I change the stored procedure with empty character for example and apply changes, than I can run it again, but only once. The second time the same problem appears again.

Please, specify the exact MySQL server version used, 5.5.x, and send the entire code of your stored procedure that demonstrates the problem.

DELIMITER $$

CREATE DEFINER=`goran6`@`localhost` PROCEDURE `TEST`()
BEGIN

	UPDATE _Brokers t1 
		JOIN qryBrokers t2 USING (BrokerID)
	SET    
		t1.BrokerName = t2.BrokerName
	WHERE
		t2.BrokerID = 3;
	
END

Yes, view can be a reason (if it is defined on SELECT with ORDER BY). Please, send the output of:

show create view qryBrokers\G

explain SELECT t1.BrokerName, t2.BrokerName 
FROM _Brokers AS t1 
JOIN qryBrokers AS t2 ON t1.BrokerID = t2.BrokerID AND t1.BrokerID = 3;

Here is the Create View for qryBrokers:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `qrybrokers` AS
    select 
        `b`.`BrokerID` AS `BrokerID`,
        `b`.`BrokerName` AS `BrokerName`,
        `c`.`Country` AS `Country`,
        `cur`.`CurrencyCode` AS `CurrencyCode`,
        `k`.`Keyword` AS `Keyword`,
        `a`.`BrokerAffiliateActive` AS `BrokerAffiliateActive`,
        `r`.`AvgBrokerRatingOverall` AS `AvgBrokerRatingOverall`,
        `r`.`CountReviews` AS `CountReviews`,
        `fbr`.`ExtUsers` AS `CountExtReviews`,
        `fbr`.`ExtRating` AS `ExtRating`,
        `bts`.`TradingPlatformTypeWeb` AS `TradingPlatformTypeWeb`,
    from
        (((((((`brokers` `b`
        left join `keywords` `k` ON ((`b`.`KeywordID` = `k`.`KeywordID`)))
        left join `countries` `c` ON ((`b`.`BrokerProfileCountryID` = `c`.`CountryID`)))
        left join `currencies` `cur` ON ((`cur`.`CurrencyID` = `b`.`BrokerFeaturesCurrencyID`)))
        left join `qrybrokersratingsavg` `r` ON ((`b`.`BrokerID` = `r`.`BrokerID`)))
        left join `qrybrokersaffiliates` `a` ON ((`b`.`BrokerAffiliateID` = `a`.`BrokerAffiliateID`)))
        left join `qrybrokerstechnologysummary` `bts` ON ((`bts`.`BrokerID` = `b`.`BrokerID`)))
        left join `qryfinalbrokerratings` `fbr` ON ((`fbr`.`BrokerID` = `b`.`BrokerID`)))
    order by `b`.`BrokerName`

YES, it has ORDER BY at the end, but I have tried to remove it and the same problem persists.

I am using the Sproc to update a record in one table (temporary table _Brokers) based on data in live table (qryBrokers).

It just came to my mind, as you can see, qryBrokers also consists out of some views (all the tables starting with qry). Could it be the problem if some of this views also has ORDER BY in it?

It just came to my mind, as you can see, qryBrokers also consists out of some views (all the tables starting with qry). Could it be the problem if some of this views also has ORDER BY in it?

SOLVED!!! 
I have removed all ORDER BY in all my views and now it works. Thanks for all your help.