MySQL中delete join 引发的危机

最新推荐文章于 2024-06-20 19:37:48 发布

飞翔的咩咩

最新推荐文章于 2024-06-20 19:37:48 发布

阅读量438

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/u012483153/article/details/112917670

版权

MySQL 专栏收录该内容

18 篇文章 2 订阅

订阅专栏

一开始是执行查询操作,执行没问题,就改为删除操作

DELETE
FROM pay_recepit pr
JOIN fbank_account_notify fan ON fan.trade_no = pr.trade_no
WHERE pr.create_time >=str_to_date('2020-01-08 11:00:00','%Y-%m-%d %H:%i:%s')
  AND fan.is_ent_deposit = 'Y';

啪,打脸了,执行不了,报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

DELETE
FROM pay_recepit pr
JOIN fbank_account_notify fan ON fan.trade_no = pr.trade_no
WHERE pr.create_time &gt
> 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 'pr
JOIN fbank_account_notify fan ON fan.trade_no = pr.trade_no
WHERE pr.create' at line 2
> 时间: 0.001s

放到navicat解释执行一下,果然失败了.因为有join,可能不知道选择哪个删除.

改为delete pr from ........执行一下果然不报错了. 所以需要指定删除的表

DELETE pr
FROM pay_recepit pr
JOIN fbank_account_notify fan ON fan.trade_no = pr.trade_no
WHERE pr.create_time >=str_to_date('2020-01-08 11:00:00','%Y-%m-%d %H:%i:%s')
  AND fan.is_ent_deposit = 'Y';

另一个是,除了使用这个 join,还可以用 exists,但是这个SQL有坑.它只会执行前面一段后面的exists与前面的没关系

DELETE
FROM pay_recepit
WHERE create_time >'2020-01-08 11:00:00'

DELETE
FROM pay_recepit
WHERE create_time >'2020-01-08 11:00:00'
  AND EXISTS
    (SELECT 1
     FROM fbank_account_notify fan
     WHERE fan.trade_no = trade_no
       AND fan.is_ent_deposit = 'Y') ;

正确的方式是像下面这样:需要指定别名

DELETE pr
FROM pay_recepit pr
WHERE pr.create_time >'2020-01-08 11:00:00'
  AND EXISTS
    (SELECT 1
     FROM fbank_account_notify fan
     WHERE fan.trade_no = pr.trade_no
       AND fan.is_ent_deposit = 'Y') ;