query optimization for update statement.

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

General SQL Server Forums

New to SQL Server Programming

query optimization for update statement.

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-09-26 : 07:34:48

Hi,

is it any possible way to simplify this below mentioned query?
update Table2 set A=1 where leadid in (select leadid from Table1 where ClientCode = 'A')
update Table2 set B=1 where leadid in (select leadid from Table1 where ClientCode = 'b')
update Table2 set C=1 where leadid in (select leadid from Table1 where ClientCode = 'c')
update Table2 set D=1 where leadid in (select leadid from Table1 where ClientCode = 'd')
update Table2 set E=1 where leadid in (select leadid from Table1 where ClientCode = 'E')
update Table2 set F=1 where leadid in (select leadid from Table1 where ClientCode = 'F')
update Table2 set G=1 where leadid in (select leadid from Table1 where ClientCode = 'G')
update Table2 set H=1 where leadid in (select leadid from Table1 where ClientCode = 'H')
update Table2 set I=1 where leadid in (select leadid from Table1 where ClientCode = 'I')
update Table2 set J=1 where leadid in (select leadid from Table1 where ClientCode = 'J')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 07:54:20

Don't know if this is any simpler/shorter, but it is probably faster.

UPDATE t2 SET
	t2.a = CASE WHEN t1.clientcode = 'A' THEN 1 ELSE t2.a END,
	t2.b = CASE WHEN t1.clientcode = 'B' THEN 1 ELSE t2.b END
	--- other columns here
FROM
	Table2 t2
	INNER JOIN Table1 t1 ON
		t1.leadid = t2.leadid
WHERE
	t1.ClientCode IN ('A','B','C','D','E','F','G','H','I','J');

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-26 : 12:03:08

[code]
UPDATE t
SET A= SIGN(ACnt),
SET B= SIGN(Bcnt),
SET C= SIGN(Ccnt),
...
FROM Table2 t
INNER JOIN (SELECT leadid,
SUM(CASE WHEN ClientCode='A' THEN 1 ELSE 0 END) AS ACnt,
SUM(CASE WHEN ClientCode='B' THEN 1 ELSE 0 END) AS BCnt,
SUM(CASE WHEN ClientCode='C' THEN 1 ELSE 0 END) AS CCnt,
...
FROM Table1
GROUP BY leadid
)t1
ON t.leadid = t1.leadid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources