April 30, 2011 at 5:44 pm
Hi guys.... suggestions needed
I have the following tables
Table : Temp3
--------------------------
Temp3ID Char 1 Char2
1 x1 NULL
2 x1 312
3 x2 420
4 x2 NULL
5 x3 NULL
6 x3 889
Table : matter
------------------------
MatterID Temp3ID
900 1
901 3
902 4
904 5
Issue: I have to delete Temp3ID's from Temp3 table which are having their Char2 as NULL. i.e (I have to delete
Records from Temp3 table having Temp3ID's {1,4,5} But Before deleting them i have to update the Temp3ID values in the 'matter' table. I have to replace Temp3ID '1' with '2' and Temp3ID '4' with '3' and Temp3ID '5' with '6'
My resultant tables should look like these
Table : Temp3
---------------------------
Temp3ID Char 1 Char2
2 x1 312
3 x2 420
6 x3 889
Table : matter
--------------------------
MatterID Temp3ID
900 2
901 3
902 3
903 6
I had solved this using CURSORS but due to performance issues i cannot use them. Is there any other way to
handle this.
Thanks in advance!!
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply