April 30, 2011 at 6:24 pm
In the code below, I may not have considered all the business logic that you have - such as can there be more than two rows in Temp3 table for a given char1, can both of them be null etc. Regardless, the query below works for the test data that you posted.--- CREATE TEST DATA.
create table #Temp3(id int, char1 varchar(32), char2 varchar(32));
create table #Matter (MatterId int, Temp3Id int);
insert into #Temp3 values
(1,'x1',NULL),
(2,'x1',312),
(3,'x2',420),
(4,'x2',NULL),
(5,'x3',NULL),
(6,'x3',889);
insert into #Matter values
(900,1),
(901,3),
(902,4),
(903,5);
-- APPLY CHANGES
begin try
begin tran
;with
CTE1 as
( select a.id as id1, b.id as id2
from #Temp3 a inner join #Temp3 b
on a.char1 = b.char1 and a.char2 is null and b.char2 is not null
)
update m set
Temp3Id = c.id2
from
#Matter m inner join CTE1 c on c.id1 = m.Temp3Id;
with
CTE1 as
( select a.id as id1, b.id as id2
from #Temp3 a inner join #Temp3 b
on a.char1 = b.char1 and a.char2 is null and b.char2 is not null
)
delete from #Temp3
from #Temp3 t inner join CTE1 c on c.id1 = t.id;
commit tran
end try
begin catch
rollback tran
end catch
select * from #temp3;
select * from #matter;
-- CLEANUP
drop table #temp3; drop table #matter;
April 30, 2011 at 7:13 pm
its working now..... Thank you so much..... 😀
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply