When you need to write IBatis involving IN Keyword for MYSQL

iBatis: Support for Array or List Parameter with SQL IN Keyword

This is a feature available in iBatis but it is not mentioned in the documentation. You can find the example in the iBatis source code under the unit tests.

Let's said I need to run the following SQL statement

select * from my_table where col_1 in ('1','2','3') 

So how do I pass in the values of 1, 2 and 3 ?

In this case you need to pass in a list parameter. The correct iBatis syntax should be

<select id="select-test" resultMap="MyTableResult"   parameterClass="list">  select * from my_table where col_1 in   <iterate open="(" close=")" conjunction=",">    #[]#   </iterate>  </select> 

And in Java you should pass in a java.util.List. E.g.

List<String> list = new ArrayList<String>(3); list.add("1"); list.add("2"); list.add("3"); List objs = sqlMapClient.queryForList("select-test",list); 

This is another example

<select id="getProducts" parameterClass="Product"         resultClass="Product">   SELECT * FROM Products   <dynamic prepend="WHERE productType IN ">     <iterate property="productTypes"              open="(" close=")"              conjunction=",">       productType=#productType#     </iterate>   </dynamic> </select> …

*P.S. Refer to the link http://twit88.com/blog/2008/03/14/ibatis-support-for-arrays-or-list-parameters-in-with-sql-in-keyword/

When you get MYSQL error: 1093

1093 You can not specify target table comments for update in FROM clause

When I try to run update query for my table "comments", MySQL returns the #1093 - You can't specify target table 'comments' for update in FROM clause message. My contrived table structure and update query are as follow:

 CREATE TABLE comments(id int primary key, phrase text, uid int);

INSERT INTO comments VALUES(1, 'admin user comments',1),
                           (2, 'HR User Comments',2),
                           (3, 'RH User Comments',2);
 
UPDATE comments
     SET phrase = (SELECT phrase FROM comments WHERE uid=2 AND id=2)
WHERE id = 3; 

Is there any easy way to work around the #1093 - You can't specify target table 'comments' for update in FROM clause error?

Answer No: 156

Actually, your above update query seems illegal as per SQL standard. MySQL does not allow to UPDATE or DELETE a table's data if you're simultaneously reading that same data with a subquery. Because you are doing so that is why MySQL tersely said its such error message. Therefore, you will have to rewrite your above update query.

Since MySQL materializes sub queries in the FROM Clause as temporary tables, wrapping the subquery into another inner subquery in the FROM Clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery. So, the update query will succeed by rewriting it like below:

 UPDATE comments
   SET phrase =( SELECT phrase FROM
	         (
	            SELECT * FROM comments
	         ) 
                 AS c1 
                 WHERE c1.uid=2 AND c1.id=2
                ) WHERE id =3;