|
nidabp
Starting Member
USA
15 Posts |
 Posted - 11/08/2012 : 07:30:32
|
Hi,
I have a source table(transaction table) with columns.
(id,bus1,bus2,bus3,sl1,sec1) based on which I need to update my target (master) table.
Master table has the same columns (id,bus1,bus2,bus2,sl1,sec1) where [id] is a primary key in the master table but not in the source table.
The update to master table should be in such a way that
1) For a particular id, if the combination of columns(bus1,bus2,bus3) in the source table are different,then (bus1,bus2,bus3) should be updated with null value in the master table.
2) For a particular id, if the column sl1 in the source table are different, then (sl1) should be updated with null value in master table.
3) The above case is repeated for column sec1 as well.
4) For a particular id,if the columns(bus1,bus2,bus3) are same,then master table should be updated with the values from the source table.
5) For a particular id, if the column(sl1) is same, or if the column(sec1) are same, master table should be updated with the same value.
I have written the query for the above conditions as:
WITH CTE
as ( SELECT id,bus1,bus2,bus3,sl1,sec1 FROM SOURCE),
CTESUB
as
(SELECT ID,COUNT(distinct(Checksum(bus1,bus2,bus3))) as BkdCount,COUNT(distinct(ISNULL(sl1,'-99'))) as BKdCount2,
COUNT(distinct(ISNULL(sec1,'-99'))) as BKdCount3 from CTE group by id)
UPDATE mast
SET bus1 = FINALCOND.bus1,
bus2 = FINALCOND.bus2,
bus3 = FINALCOND.bus3,
sl1 = FINALCOND2.sl1,
sec1 = FINALCOND3.sec1
FROM [Master] mast
LEFT OUTER JOIN
(SELECT DISTINCT main.Id,main.bus1,main.bus2,main.bus3
FROM CTE Main INNER JOIN CTESUB subMain
on Main.id = subMain.id
AND submain.BkdCount = 1) FINALCOND
ON mast.id = FINALCOND.id
LEFT OUTER JOIN
(SELECT DISTINCT Main.id,Main.sl1
FROM CTE Main INNER JOIN CTESUB SubMain2
on Main.id = SubMain2.id
AND subMain2.BKdCount2 = 1) FINALCOND2
on mast.id = FINALCOND2.id
LEFT OUTER JOIN
(SELECT DISTINCT Main.id,Main.sec1
FROM CTE Main INNER JOIN CTESUB SubMain3
on Main.id = SubMain3.id
and SubMain3.BKdCount3 = 1) FINALCOND3
on mast.id = FINALCOND3.id
The problem is that it is taking some time to run.Can this be optimized to run faster.
Any inputs are appreciated.
Thank you. |
|
Query for updating multiple columns based on count
