subqueries VS joins

gistfile1.sql

In Mysql, if your using a subquery to update a table, it will give you an error. 
e.x. In the below query your basically trying to update and query the table at the same time. This would not be possible in Mysql. 

[code language="text"]

UPDATE items ci SET ci.`c_id` = 27
WHERE ci.`ID` IN (
            SELECT c.`ID`
            FROM orders f, items c
            WHERE c.`orderID` = f.`orderID` LIMIT 100;
);

In order to solve the above issue, you would need to use a JOIN as below:

UPDATE `items` AS ci
       INNER JOIN (SELECT c.`ID`
                   FROM   orders f,
                          items c
                   WHERE c.`orderID` = f.`orderID`
                   ) AS source
               ON source.ID = ci.ID
SET ci.site_id = 127;

The above is just an illustration, you would need to fit in the correct values for this.