Query for updating multiple columns based on count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query for updating multiple columns based on count

Author

Topic

nidabp
Starting Member

15 Posts

Posted - 2012-11-08 : 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.

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-08 : 05:20:56

haii..

did you get any solution for this?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources