两个表有相同的ID关联字段,A表是供应商,B表示订单表,如果删除A表5条记录,ID分别为1,2,3,4,5,当B表里面有条记录,ID为1,此时值删除A表里面ID 为2,3,4,5
不行
追答delete from t_spcst where
SpCstID not in (select SpCstID from T_Order) and
SpCstID-0 between '1208' and '1192' 试试这个
不行
追答delete t_spcst where
SpCstID not in (select SpCstID from T_Order) and
SpCstID-0 between '1208' and '1192'
between 这个关键字,不能用在这,不合适,,我是选着性的删除,,有可能全部100条记录,,
追答delete from t_spcst a where a.SpCstID not in (select SpCstID from T_Order b) and a.SpCstID in (1208,1192)
试试吧
我是选着行的删除,而不是把所有A表里面没有B表的ID删除,我选着哪一个,就删除哪一个,如果有就不删除,没有就删除
追答delete from A
where id not in (select id from B) and id =选中行的ID
我是用的mysql数据库,执行就报错,而在SQl Server里面就可以用,不知道为什么?
追答delete from A
where not exists (select id from B where id=A.id) and id =选中行的ID
试试这样行不行
delete from t_spcst where SpCstID not in (select SpCstID from T_Order) and SpCstID in ('1208','1192')
这是打印的SQl,并没有报错,但就是没有删除数据
这个 not exists 关键字我用过了,也不行
你确定有 t_spcst 表有记录而T_Order表没有关联的记录吗,SpCstID是什么类型?
追问A表的主键bigint,确定,我都是SQl语句查出来t_spcst 表有的,而T_Order表没有的操作,我觉得与Mysql数据库有关系,奇怪sql Server 里面可以执行,为什么Mysql 里面不能执行呢?
追答bigint类型就不能这么写SpCstID in ('1208','1192') ,不能带引号!
delete from t_spcst where SpCstID not in (select SpCstID from T_Order) and SpCstID in (1208,1192)
还是不行
第一条语句执行返回消息是什么
追问
delete from t_spcst
where not SpCstID in (select SpCstID from T_Order) and SpCstID in (1208,1192)
执行报错呢?
把not 放前面:
delete from t_spcst
where (not SpCstID in (select SpCstID from T_Order)) and (SpCstID in (1208,1192))
再不行的话,可能是你Mysql版本太低,知道里有很多说低版本不支持delete条件用子查询的