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 CallerQueu a
where not exists(select * from [queue] where queueid<>a.queueid)
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-23 08:49:12
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:queueIF NOT OBJECT_ID('[queue]') IS NULL
DROP TABLE [queue]
GO
CREATE TABLE [queue]([queueid] INT,[queuename] NVARCHAR(10))
INSERT [queue]
SELECT 1000,'abc' UNION ALL
SELECT 2000,'cds' UNION ALL
SELECT 3000,'sdff' UNION ALL
SELECT 4000,'s3443'
GO
--SELECT * FROM [queue]--> 生成测试数据表:CallerQueueIF NOT OBJECT_ID('[CallerQueue]') IS NULL
DROP TABLE [CallerQueue]
GO
CREATE TABLE [CallerQueue]([QueueId] INT,[Callerid] INT)
INSERT [CallerQueue]
SELECT 1000,1 UNION ALL
SELECT 2000,2 UNION ALL
SELECT 3000,3 UNION ALL
SELECT 5000,4
GO
--SELECT * FROM [CallerQueue]-->SQL查询如下:
DELETE t
FROM CallerQueue t
WHERE NOT EXISTS (
SELECT 1
FROM queue
WHERE QueueId=t.QueueId )
SELECT * FROM CallerQueue
/*
QueueId Callerid
----------- -----------
1000 1
2000 2
3000 3(3 行受影响)
*/