sql语句update+set+select求助

Question

jsp数据库处理中
String sql=“

update buffers set lastSuccess='1' where customMoney=(select currentMoney from clothes where cloId=?) and b.cloId=?
pstmt.setString(1,cloId);
pstmt.setString(2,cloId);
pstmt.executeUpdate();

";
此语句报错，到update（）那就通不过了

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to 
use near 'lastSuccess='1' where b.customMoney=(select currentMoney from clothes where cloI' at line 1

Answer 1

update的主干语句里面 就是update xxx set xxxx=？ where xxxxxx
不会有update xxx set xxxx=？ from的写法
所以你的语句 应该是
update buffers t set lastSuccess = '1' where exists (select t1.customMoney from (select top 1 * from buffers group by cloId having cloId=? ) t1 where t1.customMoney=t.customMoney and t1.cloId=t.cloId)

另外 你的子查询中的order by 是没意义的 可能会影响执行效率