日期:2014-05-16  浏览次数:20608 次

一个简单的update语句错误
以下语句在SQL Server 2008里执行是正确的,在MySQL里执行错误,
我对MySQL不熟悉,请大家指教指教!

update goods_combine set sn = T.minID
from (select min(id) minID, sn from goods_combine group by sn)T
where goods_combine.sn = T.sn

在MySQL里执行错误:
[Err] 1064 - 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 'from (select min(id) minID, sn from goods_combine group by sn)T
where goods_com' at line 2


表结构:
CREATE TABLE `goods_combine` 
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '".iconv("gbk","utf-8","联合ID")."',
  sn VARCHAR(64) NOT NULL COMMENT '".iconv("gbk","utf-8","序号(联合编号)")."',
  goods_id INT NOT NULL COMMENT '".iconv("gbk","utf-8","实物ID")."'
)

------解决方案--------------------
update goods_combine a 
inner join 
(select min(id) minID, sn from goods_combine group by sn) T
on goods_combine.sn = T.sn
set sn = T.minID