如何在mysql的删除/更新查询中使用子查询 [英] How to use subquery in delete/update query in mysql

问题描述

我正在使用使用查询的 mysql 5.0.77 数据库:

I am using a mysql 5.0.77 database using the query:

Delete from IPADDRESS
where visitdate Not in (SELECT max(visitdate) FROM IPADDRESS WHERE USERNAME='MGSH0002') 
and USERNAME='MGSH0002' 

我在执行时收到此错误:

I am getting this error when executed:

不能在 from 子句中为更新指定目标表 IPADDRESS

You can't specify target table IPADDRESS for update in from clause

推荐答案

当然不是最好的解决方案，但对于您的问题，这可以解决问题:

Certainly not the best solution but for your problem this will do the trick:

delete i1 from
  IPADDRESS i1,
  IPADDRESS i2
where
  i1.username = i2.username
  and i1.username = 'MGSH0002'
  and i1.visitdate < i2.visitdate

另一种更智能的解决方案是以下语句:

An alternative and much more smarter solution is the following statement:

delete i1 from
  IPADDRESS i1,
  (select max(visitdate) visitdate from IPADDRESS where username = 'MGSH0002') temp
where
  i1.username = 'MGSH0002'
  and i1.visitdate < temp.visitdate

这篇关于如何在mysql的删除/更新查询中使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！