使用子查询MySQL删除

Question

我正在尝试删除一个子查询，该子查询与在SQL Server中工作的表'carretilla‘相同

DELETE FROM carretilla 
WHERE carcod IN (
  SELECT carcod  
  FROM carretilla 
  WHERE TIMESTAMPDIFF(MINUTE,carfch, NOW()) > 10 
  group BY carcod
);

我该怎么做呢？谢谢

Answer 1

AFAIK您不能直接引用您从中删除或更新的表，但是如果您使用子查询，服务器将创建一个临时记录集并让您执行删除操作，因此这应该是可行的：

DELETE FROM carretilla 
WHERE carcod IN (
  SELECT carcod FROM (
    SELECT carcod  FROM carretilla 
    WHERE TIMESTAMPDIFF(MINUTE,carfch, NOW()) > 10 group BY carcod
  ) temp_table
);

请参阅此sample SQL Fiddle。