queue表
queueid queuename
1000 abc
2000 cds
3000 sdff
4000 s3443CallerQueue表
QueueId Callerid
1000 1
2000 2
3000 3
5000 4如上表,要求删除callerqueue表里面queueid在queue表里面不存在的记录(如上表应删除queueid=5000这条记录),这个sql语句怎么写?
queueid queuename
1000 abc
2000 cds
3000 sdff
4000 s3443CallerQueue表
QueueId Callerid
1000 1
2000 2
3000 3
5000 4如上表,要求删除callerqueue表里面queueid在queue表里面不存在的记录(如上表应删除queueid=5000这条记录),这个sql语句怎么写?
where QueueId not in (select queueid from queue )
FROM CallerQueue T1
WHERE NOT EXISTS(SELECT 1 FROM queue T2 WHERE T2.QueueId=T1.QueueId)
callerqueue C WHERE NOT EXISTS(SELECT 1 FROM queue WHERE queueid=C.queueid)
from CallerQueu a
where not exists(select * from [queue] where queueid<>a.queueid)
DELETE FROM CallerQueue
WHERE QueueId<>ANY(SELECT queueid from queue)或者像1L那样
FROM CallerQueue T1
WHERE NOT EXISTS(SELECT 1 FROM queue T2 WHERE T2.QueueId=T1.QueueId)
多了个FROM
create table [queue]
(
queueid char(10),
queuename char(10)
)insert [queue]
select '1000','abc' union all
select '2000','cds' union all
select '3000','sdff' union all
select '4000','s344'if object_id('[CallerQueue]') is not null drop table [CallerQueue]
create table [CallerQueue]
(
queueid char(10),
Callerid int
)insert [CallerQueue]
select '1000',1 union all
select '2000',2 union all
select '3000',3 union all
select '5000',4delete from [CallerQueue]
where checksum(queueid)not in (select checksum(queueid)from [queue])select * from [CallerQueue]