March 4, 2010 at 5:30 am
My tables and rows as follow,
declare @t1 table
(
idx smallint,
desn varchar(50)
)
insert into @t1 values(2,'gopeng');
insert into @t1 values(2,'sungai rokam');
insert into @t1 values(2,'sungai manik');
declare @t2 table
(
idx smallint,
desn varchar(50)
)
insert into @t2 values(2,'hpt');
insert into @t2 values(2,'sungai rokam');
insert into @t2 values(2,'slim river');
Me query as follow,
insert into @t1
SELECT * FROM @t2
EXCEPT
SELECT * FROM @t1
@t1 as follow,
2gopeng
2sungai rokam
2sungai manik
2hpt
2slim river
Me query as follow,
SELECT * FROM @t1
EXCEPT
SELECT * FROM @t2
my 2nd resultset was,
2gopeng
2sungai manik
My question is
1. How to delete @t1 with 2nd resultset row?
2. So my result was in @t1 as follow,
2sungai rokam
2hpt
2slim river
March 4, 2010 at 5:52 am
DELETE FROM @T1 where desn IN(
SELECT desn FROM @t1
EXCEPT
SELECT desn FROM @t2)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2010 at 6:59 am
DELETE
FROM @t1
WHERE NOT EXISTS
(
SELECT *
FROM @t2
WHERE [@t2].idx = [@t1].idx
AND [@t2].desn = [@t1].desn
);
@Bhuvnesh: your code is not reliable since 'desn' is not a primary or unique key 😉
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2010 at 10:19 am
both of you are great.
all answers is my inspiration
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply