直接执行以下sql报错:
You can't specify target table 'TStopInfo' for update in FROM clause
UPDATE TStopInfo set EndTime=NOW() WHERE ID= (SELECT MAX(ID) FROM TStopInfo);
改为:
UPDATE TStopInfo
INNER JOIN (SELECT MAX(ID) AS MaxID FROM TStopInfo) AS SubQuery
SET EndTime=NOW()
WHERE ID = SubQuery.MaxID;
如果在查询软件使用以下,是没有错的,但是放在C#中,执行sql会报错,要么就要写成存储过程,但是这么简单的没必要:
SET @max_id = (SELECT MAX(ID) FROM TStopInfo);
UPDATE TStopInfo set EndTime=NOW() WHERE ID=@max_id;
另外一种写法,多包一层,也是可以的:
UPDATE TStopInfo set EndTime=NOW()
WHERE ID= (select * from (SELECT MAX(ID) FROM TStopInfo) t1);