SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query for updating multiple columns based on count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nidabp
Starting Member

USA
15 Posts

Posted - 11/08/2012 :  07:30:32  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/08/2013 :  05:20:56  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000