SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimize Update Statement


Optimize Update Statement

Author
Message
wit_jp2001
wit_jp2001
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 20
Wondering what I can do to speed up this update. It is long and I am sure there is an easier way to get this running.

I ran SQL Profiler and this query in particular kills the speed of my asp code. Duration of Query in Profiler is 202737
I can post tables if needed, but it should be pretty straight forward.
Any help would be great.

DECLARE @tempS AS nvarchar(150) DECLARE @tempW AS nvarchar(50) DECLARE @tempQ As nvarchar(5) Update dbo.EXPORTMAIN SET @tempS = SKU1, @tempW = WHSELOC1, @tempQ = QTY1,SKU1 = Case When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU2 When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU3 When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU4 When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU5 When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU6 When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU7 When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU8 When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU9 When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU10 When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU11 When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU12 When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU13 When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU14 When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU15 else 'S??' End,WHSELOC1 = Case When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC2 When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC3 When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC4 When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC5 When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC6 When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC7 When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC8 When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC9 When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC10 When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC11 When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC12 When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC13 When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC14 When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC15 else 'W??' End,QTY1 = Case When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY2 When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY3 When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY4 When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY5 When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY6 When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY7 When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY8 When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY9 When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY10 When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY11 When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY12 When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY13 When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY14 When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY15 else '999' End,QTY2 = Case When SKU2 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY2 When QTY2 = 0 Then 0 else @tempQ End,SKU2 = Case When SKU2 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU2 When SKU2 = NULL Then NULL else @tempS End,WHSELOC2 = Case When SKU2 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC2 When WHSELOC2 = NULL Then NULL else @tempW End,QTY3 = Case When SKU3 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY3 When QTY3 = 0 Then 0 else @tempQ End,SKU3 = Case When SKU3 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU3 When SKU3 = NULL Then NULL else @tempS End,WHSELOC3 = Case When SKU3 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC3 When WHSELOC3 = NULL Then NULL else @tempW End,QTY4 = Case When SKU4 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY4 When QTY4 = 0 Then 0 else @tempQ End,SKU4 = Case When SKU4 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU4 When SKU4 = NULL Then NULL else @tempS End,WHSELOC4 = Case When SKU4 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC4 When WHSELOC4 = NULL Then NULL else @tempW End,QTY5 = Case When SKU5 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY5 When QTY5 = 0 Then 0 else @tempQ End,SKU5 = Case When SKU5 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU5 When SKU5 = NULL Then NULL else @tempS End,WHSELOC5 = Case When SKU5 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC5 When WHSELOC5 = NULL Then NULL else @tempW End,QTY6 = Case When SKU6 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY6 When QTY6 = 0 Then 0 else @tempQ End,SKU6 = Case When SKU6 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU6 When SKU6 = NULL Then NULL else @tempS End,WHSELOC6 = Case When SKU6 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC6 When WHSELOC6 = NULL Then NULL else @tempW End,QTY7 = Case When SKU7 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY7 When QTY7 = 0 Then 0 else @tempQ End,SKU7 = Case When SKU7 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU7 When SKU7 = NULL Then NULL else @tempS End,WHSELOC7 = Case When SKU7 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC7 When WHSELOC7 = NULL Then NULL else @tempW End,QTY8 = Case When SKU8 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY8 When QTY8 = 0 Then 0 else @tempQ End,SKU8 = Case When SKU8 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU8 When SKU8 = NULL Then NULL else @tempS End,WHSELOC8 = Case When SKU8 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC8 When WHSELOC8 = NULL Then NULL else @tempW End,QTY9 = Case When SKU9 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY9 When QTY9 = 0 Then 0 else @tempQ End,SKU9 = Case When SKU9 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU9 When SKU9 = NULL Then NULL else @tempS End,WHSELOC9 = Case When SKU9 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC9 When WHSELOC9 = NULL Then NULL else @tempW End,QTY10 = Case When SKU10 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY10 When QTY10 = 0 Then 0 else @tempQ End,SKU10 = Case When SKU10 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU10 When SKU10 = NULL Then NULL else @tempS End,WHSELOC10 = Case When SKU10 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC10 When WHSELOC10 = NULL Then NULL else @tempW End,QTY11 = Case When SKU11 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY11 When QTY11 = 0 Then 0 else @tempQ End,SKU11 = Case When SKU11 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU11 When SKU11 = NULL Then NULL else @tempS End,WHSELOC11 = Case When SKU11 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC11 When WHSELOC11 = NULL Then NULL else @tempW End,QTY12 = Case When SKU12 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY12 When QTY12 = 0 Then 0 else @tempQ End,SKU12 = Case When SKU12 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU12 When SKU12 = NULL Then NULL else @tempS End,WHSELOC12 = Case When SKU12 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC12 When WHSELOC12 = NULL Then NULL else @tempW End,QTY13 = Case When SKU13 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY13 When QTY13 = 0 Then 0 else @tempQ End,SKU13 = Case When SKU13 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU13 When SKU13 = NULL Then NULL else @tempS End,WHSELOC13 = Case When SKU13 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC13 When WHSELOC13 = NULL Then NULL else @tempW End,QTY14 = Case When SKU14 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY14 When QTY14 = 0 Then 0 else @tempQ End,SKU14 = Case When SKU14 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU14 When SKU14 = NULL Then NULL else @tempS End,WHSELOC14 = Case When SKU14 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC14 When WHSELOC14 = NULL Then NULL else @tempW End,QTY15 = Case When SKU15 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY15 When QTY15 = 0 Then 0 else @tempQ End,SKU15 = Case When SKU15 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU15 When SKU15 = NULL Then NULL else @tempS End, WHSELOC15 = Case When SKU15 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC15 When WHSELOC15 = NULL Then NULL else @tempW End WHERE UPLOADDATE = '" & sqluHd & "' AND ID IN (SELECT ID from dbo.UPDATEHROW1 WHERE MaxVal <> 0)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42134 Visits: 20009
Formatted for readability:
-- Formatted
DECLARE @tempS AS nvarchar(150)
DECLARE @tempW AS nvarchar(50)
DECLARE @tempQ As nvarchar(5)

UPDATE dbo.EXPORTMAIN SET
@tempS = SKU1,
@tempW = WHSELOC1,
@tempQ = QTY1,
SKU1 = Case
When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU2
When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU3
When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU4
When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU5
When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU6
When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU7
When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU8
When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU9
When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU10
When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU11
When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU12
When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU13
When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU14
When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU15
else 'S??' End,
WHSELOC1 = Case
When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC2
When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC3
When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC4
When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC5
When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC6
When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC7
When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC8
When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC9
When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC10
When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC11
When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC12
When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC13
When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC14
When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC15
else 'W??' End,
QTY1 = Case
When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY2
When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY3
When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY4
When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY5
When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY6
When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY7
When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY8
When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY9
When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY10
When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY11
When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY12
When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY13
When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY14
When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY15
else '999' End,
QTY2 = Case
When SKU2 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY2
When QTY2 = 0 Then 0
else @tempQ End,
SKU2 = Case
When SKU2 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU2
When SKU2 = NULL Then NULL
else @tempS End,
WHSELOC2 = Case
When SKU2 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC2
When WHSELOC2 = NULL Then NULL
else @tempW End,
QTY3 = Case
When SKU3 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY3
When QTY3 = 0 Then 0
else @tempQ End,
SKU3 = Case
When SKU3 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU3
When SKU3 = NULL Then NULL
else @tempS End,
WHSELOC3 = Case
When SKU3 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC3
When WHSELOC3 = NULL Then NULL
else @tempW End,
QTY4 = Case
When SKU4 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY4
When QTY4 = 0 Then 0
else @tempQ End,
SKU4 = Case
When SKU4 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU4
When SKU4 = NULL Then NULL
else @tempS End,
WHSELOC4 = Case
When SKU4 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC4
When WHSELOC4 = NULL Then NULL
else @tempW End,
QTY5 = Case
When SKU5 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY5
When QTY5 = 0 Then 0
else @tempQ End,
SKU5 = Case
When SKU5 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU5
When SKU5 = NULL Then NULL
else @tempS End,
WHSELOC5 = Case
When SKU5 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC5
When WHSELOC5 = NULL Then NULL
else @tempW End,
QTY6 = Case
When SKU6 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY6
When QTY6 = 0 Then 0
else @tempQ End,
SKU6 = Case
When SKU6 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU6
When SKU6 = NULL Then NULL
else @tempS End,
WHSELOC6 = Case
When SKU6 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC6
When WHSELOC6 = NULL Then NULL
else @tempW End,
QTY7 = Case
When SKU7 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY7
When QTY7 = 0 Then 0
else @tempQ End,
SKU7 = Case
When SKU7 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU7
When SKU7 = NULL Then NULL
else @tempS End,
WHSELOC7 = Case
When SKU7 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC7
When WHSELOC7 = NULL Then NULL
else @tempW End,
QTY8 = Case
When SKU8 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY8
When QTY8 = 0 Then 0
else @tempQ End,
SKU8 = Case
When SKU8 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU8
When SKU8 = NULL Then NULL
else @tempS End,
WHSELOC8 = Case
When SKU8 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC8
When WHSELOC8 = NULL Then NULL
else @tempW End,
QTY9 = Case
When SKU9 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY9
When QTY9 = 0 Then 0
else @tempQ End,
SKU9 = Case
When SKU9 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU9
When SKU9 = NULL Then NULL
else @tempS End,
WHSELOC9 = Case
When SKU9 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC9
When WHSELOC9 = NULL Then NULL
else @tempW End,
QTY10 = Case
When SKU10 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY10
When QTY10 = 0 Then 0
else @tempQ End,
SKU10 = Case
When SKU10 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU10
When SKU10 = NULL Then NULL
else @tempS End,
WHSELOC10 = Case
When SKU10 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC10
When WHSELOC10 = NULL Then NULL
else @tempW End,
QTY11 = Case
When SKU11 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY11
When QTY11 = 0 Then 0
else @tempQ End,
SKU11 = Case
When SKU11 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU11
When SKU11 = NULL Then NULL
else @tempS End,
WHSELOC11 = Case
When SKU11 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC11
When WHSELOC11 = NULL Then NULL
else @tempW End,
QTY12 = Case
When SKU12 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY12
When QTY12 = 0 Then 0
else @tempQ End,
SKU12 = Case
When SKU12 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU12
When SKU12 = NULL Then NULL
else @tempS End,
WHSELOC12 = Case
When SKU12 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC12
When WHSELOC12 = NULL Then NULL
else @tempW End,
QTY13 = Case
When SKU13 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY13
When QTY13 = 0 Then 0
else @tempQ End,
SKU13 = Case
When SKU13 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU13
When SKU13 = NULL Then NULL
else @tempS End,
WHSELOC13 = Case
When SKU13 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC13
When WHSELOC13 = NULL Then NULL
else @tempW End,
QTY14 = Case
When SKU14 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY14
When QTY14 = 0 Then 0
else @tempQ End,
SKU14 = Case
When SKU14 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU14
When SKU14 = NULL Then NULL
else @tempS End,
WHSELOC14 = Case
When SKU14 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC14
When WHSELOC14 = NULL Then NULL
else @tempW End,
QTY15 = Case
When SKU15 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY15
When QTY15 = 0 Then 0
else @tempQ End,
SKU15 = Case
When SKU15 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU15
When SKU15 = NULL Then NULL
else @tempS End,
WHSELOC15 = Case
When SKU15 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC15
When WHSELOC15 = NULL Then NULL
else @tempW End
WHERE UPLOADDATE = '" & sqluHd & "'
AND ID IN (SELECT ID from dbo.UPDATEHROW1 WHERE MaxVal <> 0)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42134 Visits: 20009
Same statement rewritten as UPDATE FROM, with nonsense logic commented out:

-- Formatted
DECLARE @tempS AS nvarchar(150)
DECLARE @tempW AS nvarchar(50)
DECLARE @tempQ As nvarchar(5)

UPDATE e SET
@tempS = SKU1, -- collect value of SKU1 before any updates are applied to this row
@tempW = WHSELOC1,
@tempQ = QTY1,
SKU1 = Case
When SKU2 = u.SKUVAL Then SKU2
When SKU3 = u.SKUVAL Then SKU3
When SKU4 = u.SKUVAL Then SKU4
When SKU5 = u.SKUVAL Then SKU5
When SKU6 = u.SKUVAL Then SKU6
When SKU7 = u.SKUVAL Then SKU7
When SKU8 = u.SKUVAL Then SKU8
When SKU9 = u.SKUVAL Then SKU9
When SKU10 = u.SKUVAL Then SKU10
When SKU11 = u.SKUVAL Then SKU11
When SKU12 = u.SKUVAL Then SKU12
When SKU13 = u.SKUVAL Then SKU13
When SKU14 = u.SKUVAL Then SKU14
When SKU15 = u.SKUVAL Then SKU15
else 'S??' End,
WHSELOC1 = Case
When SKU2 = u.SKUVAL Then WHSELOC2
When SKU3 = u.SKUVAL Then WHSELOC3
When SKU4 = u.SKUVAL Then WHSELOC4
When SKU5 = u.SKUVAL Then WHSELOC5
When SKU6 = u.SKUVAL Then WHSELOC6
When SKU7 = u.SKUVAL Then WHSELOC7
When SKU8 = u.SKUVAL Then WHSELOC8
When SKU9 = u.SKUVAL Then WHSELOC9
When SKU10 = u.SKUVAL Then WHSELOC10
When SKU11 = u.SKUVAL Then WHSELOC11
When SKU12 = u.SKUVAL Then WHSELOC12
When SKU13 = u.SKUVAL Then WHSELOC13
When SKU14 = u.SKUVAL Then WHSELOC14
When SKU15 = u.SKUVAL Then WHSELOC15
else 'W??' End,
QTY1 = Case
When SKU2 = u.SKUVAL Then QTY2
When SKU3 = u.SKUVAL Then QTY3
When SKU4 = u.SKUVAL Then QTY4
When SKU5 = u.SKUVAL Then QTY5
When SKU6 = u.SKUVAL Then QTY6
When SKU7 = u.SKUVAL Then QTY7
When SKU8 = u.SKUVAL Then QTY8
When SKU9 = u.SKUVAL Then QTY9
When SKU10 = u.SKUVAL Then QTY10
When SKU11 = u.SKUVAL Then QTY11
When SKU12 = u.SKUVAL Then QTY12
When SKU13 = u.SKUVAL Then QTY13
When SKU14 = u.SKUVAL Then QTY14
When SKU15 = u.SKUVAL Then QTY15
else '999' End,
QTY2 = Case
When SKU2 <> u.SKUVAL Then QTY2
When QTY2 = 0 Then 0
else @tempQ End,
SKU2 = Case
When SKU2 <> u.SKUVAL Then SKU2
--When SKU2 = NULL Then NULL
else @tempS End,
WHSELOC2 = Case
When SKU2 <> u.SKUVAL Then WHSELOC2
--When WHSELOC2 = NULL Then NULL
else @tempW End,
QTY3 = Case
When SKU3 <> u.SKUVAL Then QTY3
When QTY3 = 0 Then 0
else @tempQ End,
SKU3 = Case
When SKU3 <> u.SKUVAL Then SKU3
--When SKU3 = NULL Then NULL
else @tempS End,
WHSELOC3 = Case
When SKU3 <> u.SKUVAL Then WHSELOC3
--When WHSELOC3 = NULL Then NULL
else @tempW End,
QTY4 = Case
When SKU4 <> u.SKUVAL Then QTY4
When QTY4 = 0 Then 0
else @tempQ End,
SKU4 = Case
When SKU4 <> u.SKUVAL Then SKU4
--When SKU4 = NULL Then NULL
else @tempS End,
WHSELOC4 = Case
When SKU4 <> u.SKUVAL Then WHSELOC4
--When WHSELOC4 = NULL Then NULL
else @tempW End,
QTY5 = Case
When SKU5 <> u.SKUVAL Then QTY5
When QTY5 = 0 Then 0
else @tempQ End,
SKU5 = Case
When SKU5 <> u.SKUVAL Then SKU5
--When SKU5 = NULL Then NULL
else @tempS End,
WHSELOC5 = Case
When SKU5 <> u.SKUVAL Then WHSELOC5
--When WHSELOC5 = NULL Then NULL
else @tempW End,
QTY6 = Case
When SKU6 <> u.SKUVAL Then QTY6
When QTY6 = 0 Then 0
else @tempQ End,
SKU6 = Case
When SKU6 <> u.SKUVAL Then SKU6
--When SKU6 = NULL Then NULL
else @tempS End,
WHSELOC6 = Case
When SKU6 <> u.SKUVAL Then WHSELOC6
--When WHSELOC6 = NULL Then NULL
else @tempW End,
QTY7 = Case
When SKU7 <> u.SKUVAL Then QTY7
When QTY7 = 0 Then 0
else @tempQ End,
SKU7 = Case
When SKU7 <> u.SKUVAL Then SKU7
--When SKU7 = NULL Then NULL
else @tempS End,
WHSELOC7 = Case
When SKU7 <> u.SKUVAL Then WHSELOC7
--When WHSELOC7 = NULL Then NULL
else @tempW End,
QTY8 = Case
When SKU8 <> u.SKUVAL Then QTY8
When QTY8 = 0 Then 0
else @tempQ End,
SKU8 = Case
When SKU8 <> u.SKUVAL Then SKU8
--When SKU8 = NULL Then NULL
else @tempS End,
WHSELOC8 = Case
When SKU8 <> u.SKUVAL Then WHSELOC8
--When WHSELOC8 = NULL Then NULL
else @tempW End,
QTY9 = Case
When SKU9 <> u.SKUVAL Then QTY9
When QTY9 = 0 Then 0
else @tempQ End,
SKU9 = Case
When SKU9 <> u.SKUVAL Then SKU9
--When SKU9 = NULL Then NULL
else @tempS End,
WHSELOC9 = Case
When SKU9 <> u.SKUVAL Then WHSELOC9
--When WHSELOC9 = NULL Then NULL
else @tempW End,
QTY10 = Case
When SKU10 <> u.SKUVAL Then QTY10
When QTY10 = 0 Then 0
else @tempQ End,
SKU10 = Case
When SKU10 <> u.SKUVAL Then SKU10
--When SKU10 = NULL Then NULL
else @tempS End,
WHSELOC10 = Case
When SKU10 <> u.SKUVAL Then WHSELOC10
--When WHSELOC10 = NULL Then NULL
else @tempW End,
QTY11 = Case
When SKU11 <> u.SKUVAL Then QTY11
When QTY11 = 0 Then 0
else @tempQ End,
SKU11 = Case
When SKU11 <> u.SKUVAL Then SKU11
--When SKU11 = NULL Then NULL
else @tempS End,
WHSELOC11 = Case
When SKU11 <> u.SKUVAL Then WHSELOC11
--When WHSELOC11 = NULL Then NULL
else @tempW End,
QTY12 = Case
When SKU12 <> u.SKUVAL Then QTY12
When QTY12 = 0 Then 0
else @tempQ End,
SKU12 = Case
When SKU12 <> u.SKUVAL Then SKU12
--When SKU12 = NULL Then NULL
else @tempS End,
WHSELOC12 = Case
When SKU12 <> u.SKUVAL Then WHSELOC12
--When WHSELOC12 = NULL Then NULL
else @tempW End,
QTY13 = Case
When SKU13 <> u.SKUVAL Then QTY13
When QTY13 = 0 Then 0
else @tempQ End,
SKU13 = Case
When SKU13 <> u.SKUVAL Then SKU13
--When SKU13 = NULL Then NULL
else @tempS End,
WHSELOC13 = Case
When SKU13 <> u.SKUVAL Then WHSELOC13
--When WHSELOC13 = NULL Then NULL
else @tempW End,
QTY14 = Case
When SKU14 <> u.SKUVAL Then QTY14
When QTY14 = 0 Then 0
else @tempQ End,
SKU14 = Case
When SKU14 <> u.SKUVAL Then SKU14
--When SKU14 = NULL Then NULL
else @tempS End,
WHSELOC14 = Case
When SKU14 <> u.SKUVAL Then WHSELOC14
--When WHSELOC14 = NULL Then NULL
else @tempW End,
QTY15 = Case
When SKU15 <> u.SKUVAL Then QTY15
When QTY15 = 0 Then 0
else @tempQ End,
SKU15 = Case
When SKU15 <> u.SKUVAL Then SKU15
--When SKU15 = NULL Then NULL
else @tempS End,
WHSELOC15 = Case
When SKU15 <> u.SKUVAL Then WHSELOC15
--When WHSELOC15 = NULL Then NULL
else @tempW End

FROM dbo.EXPORTMAIN e
LEFT JOIN dbo.UPDATEHROW1 u ON u.ID = e.ID
WHERE e.UPLOADDATE = '" & sqluHd & "'
AND e.ID IN (SELECT ID from dbo.UPDATEHROW1 WHERE MaxVal <> 0)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42134 Visits: 20009
If you're wondering what the variables are for in the UPDATE, they collect some column values as they are before any updates have been performed:

DROP TABLE #EXPORTMAIN
CREATE TABLE #EXPORTMAIN (
ID INT IDENTITY(1,1),
SKU1 nvarchar(150),
SKU2 nvarchar(150),
WHSELOC1 nvarchar(50),
WHSELOC2 nvarchar(50),
QTY1 nvarchar(5),
QTY2 nvarchar(5))
INSERT INTO #EXPORTMAIN (SKU1, WHSELOC1, QTY1)
SELECT '0001', 'WH1', '1' UNION ALL
SELECT '0002', 'WH1', '2' UNION ALL
SELECT '0003', 'WH1', '3' UNION ALL
SELECT '0001', 'WH1', '4' UNION ALL
SELECT '0002', 'WH1', '5' UNION ALL
SELECT '0001', 'WH2', '6' UNION ALL
SELECT '0002', 'WH2', '7' UNION ALL
SELECT '0003', 'WH2', '8' UNION ALL
SELECT '0001', 'WH2', '9' UNION ALL
SELECT '0002', 'WH2', '10'

SELECT * FROM #EXPORTMAIN

-- Formatted
DECLARE @tempS AS nvarchar(150)
DECLARE @tempW AS nvarchar(50)
DECLARE @tempQ As nvarchar(5)

UPDATE e SET
@tempS = SKU1, -- value of SKU1 before any update
@tempW = WHSELOC1,
@tempQ = QTY1,
SKU1 = SKU1+WHSELOC1, -- change SKU1
SKU2 = @tempS, -- change SKU2 to the initial value of SKU1
WHSELOC2 = @tempW,
QTY2 = @tempQ
FROM #EXPORTMAIN e

SELECT * FROM #EXPORTMAIN



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99553 Visits: 33014
Nice job Chris. I'd love to see what the execution plan of the original query looked like.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10442 Visits: 5157
ChrisM@Work (1/24/2014)
Formatted for readability:
-- Formatted
DECLARE @tempS AS nvarchar(150)
DECLARE @tempW AS nvarchar(50)
DECLARE @tempQ As nvarchar(5)

UPDATE dbo.EXPORTMAIN SET
@tempS = SKU1,
@tempW = WHSELOC1,
@tempQ = QTY1,
SKU1 = Case
When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU2
When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU3
When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU4
When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU5
When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU6
When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU7
When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU8
When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU9
When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU10
When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU11
When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU12
When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU13
When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU14
When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU15
else 'S??' End,
WHSELOC1 = Case
When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC2
When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC3
When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC4
When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC5
When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC6
When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC7
When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC8
When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC9
When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC10
When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC11
When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC12
When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC13
When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC14
When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC15
else 'W??' End,
QTY1 = Case
When SKU2 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY2
When SKU3 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY3
When SKU4 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY4
When SKU5 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY5
When SKU6 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY6
When SKU7 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY7
When SKU8 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY8
When SKU9 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY9
When SKU10 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY10
When SKU11 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY11
When SKU12 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY12
When SKU13 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY13
When SKU14 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY14
When SKU15 = (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then QTY15
else '999' End,
QTY2 = Case
When SKU2 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY2
When QTY2 = 0 Then 0
else @tempQ End,
SKU2 = Case
When SKU2 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU2
When SKU2 = NULL Then NULL
else @tempS End,
WHSELOC2 = Case
When SKU2 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC2
When WHSELOC2 = NULL Then NULL
else @tempW End,
QTY3 = Case
When SKU3 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY3
When QTY3 = 0 Then 0
else @tempQ End,
SKU3 = Case
When SKU3 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU3
When SKU3 = NULL Then NULL
else @tempS End,
WHSELOC3 = Case
When SKU3 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC3
When WHSELOC3 = NULL Then NULL
else @tempW End,
QTY4 = Case
When SKU4 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY4
When QTY4 = 0 Then 0
else @tempQ End,
SKU4 = Case
When SKU4 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU4
When SKU4 = NULL Then NULL
else @tempS End,
WHSELOC4 = Case
When SKU4 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC4
When WHSELOC4 = NULL Then NULL
else @tempW End,
QTY5 = Case
When SKU5 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY5
When QTY5 = 0 Then 0
else @tempQ End,
SKU5 = Case
When SKU5 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU5
When SKU5 = NULL Then NULL
else @tempS End,
WHSELOC5 = Case
When SKU5 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC5
When WHSELOC5 = NULL Then NULL
else @tempW End,
QTY6 = Case
When SKU6 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY6
When QTY6 = 0 Then 0
else @tempQ End,
SKU6 = Case
When SKU6 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU6
When SKU6 = NULL Then NULL
else @tempS End,
WHSELOC6 = Case
When SKU6 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC6
When WHSELOC6 = NULL Then NULL
else @tempW End,
QTY7 = Case
When SKU7 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY7
When QTY7 = 0 Then 0
else @tempQ End,
SKU7 = Case
When SKU7 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU7
When SKU7 = NULL Then NULL
else @tempS End,
WHSELOC7 = Case
When SKU7 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC7
When WHSELOC7 = NULL Then NULL
else @tempW End,
QTY8 = Case
When SKU8 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY8
When QTY8 = 0 Then 0
else @tempQ End,
SKU8 = Case
When SKU8 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU8
When SKU8 = NULL Then NULL
else @tempS End,
WHSELOC8 = Case
When SKU8 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC8
When WHSELOC8 = NULL Then NULL
else @tempW End,
QTY9 = Case
When SKU9 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY9
When QTY9 = 0 Then 0
else @tempQ End,
SKU9 = Case
When SKU9 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU9
When SKU9 = NULL Then NULL
else @tempS End,
WHSELOC9 = Case
When SKU9 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC9
When WHSELOC9 = NULL Then NULL
else @tempW End,
QTY10 = Case
When SKU10 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY10
When QTY10 = 0 Then 0
else @tempQ End,
SKU10 = Case
When SKU10 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU10
When SKU10 = NULL Then NULL
else @tempS End,
WHSELOC10 = Case
When SKU10 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC10
When WHSELOC10 = NULL Then NULL
else @tempW End,
QTY11 = Case
When SKU11 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY11
When QTY11 = 0 Then 0
else @tempQ End,
SKU11 = Case
When SKU11 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU11
When SKU11 = NULL Then NULL
else @tempS End,
WHSELOC11 = Case
When SKU11 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC11
When WHSELOC11 = NULL Then NULL
else @tempW End,
QTY12 = Case
When SKU12 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY12
When QTY12 = 0 Then 0
else @tempQ End,
SKU12 = Case
When SKU12 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU12
When SKU12 = NULL Then NULL
else @tempS End,
WHSELOC12 = Case
When SKU12 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC12
When WHSELOC12 = NULL Then NULL
else @tempW End,
QTY13 = Case
When SKU13 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY13
When QTY13 = 0 Then 0
else @tempQ End,
SKU13 = Case
When SKU13 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU13
When SKU13 = NULL Then NULL
else @tempS End,
WHSELOC13 = Case
When SKU13 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC13
When WHSELOC13 = NULL Then NULL
else @tempW End,
QTY14 = Case
When SKU14 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY14
When QTY14 = 0 Then 0
else @tempQ End,
SKU14 = Case
When SKU14 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU14
When SKU14 = NULL Then NULL
else @tempS End,
WHSELOC14 = Case
When SKU14 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC14
When WHSELOC14 = NULL Then NULL
else @tempW End,
QTY15 = Case
When SKU15 <> (Select SKUVAL from dbo.UPDATEHROW1 Where ID = dbo.EXPORTMAIN.ID) Then QTY15
When QTY15 = 0 Then 0
else @tempQ End,
SKU15 = Case
When SKU15 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then SKU15
When SKU15 = NULL Then NULL
else @tempS End,
WHSELOC15 = Case
When SKU15 <> (SELECT SKUVAL from dbo.UPDATEHROW1 WHERE ID = dbo.EXPORTMAIN.ID) Then WHSELOC15
When WHSELOC15 = NULL Then NULL
else @tempW End
WHERE UPLOADDATE = '" & sqluHd & "'
AND ID IN (SELECT ID from dbo.UPDATEHROW1 WHERE MaxVal <> 0)




Hi Cris,
What do you use for formatting such t-sql texts?

Regards,
IgorMi

Igor Micev,
My blog: www.igormicev.com
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42134 Visits: 20009
IgorMi, you can use http://extras.sqlservercentral.com/prettifier/prettifier.aspx.

I tend not to. If I'm about to spend a few minutes reading and understanding a chunk of code, reformatting it easily becomes part of the process.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42134 Visits: 20009
Grant Fritchey (1/24/2014)
Nice job Chris. I'd love to see what the execution plan of the original query looked like.


Thanks Grant. Me too, there are over 70 reads of the lookup.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99553 Visits: 33014
ChrisM@Work (1/24/2014)
Grant Fritchey (1/24/2014)
Nice job Chris. I'd love to see what the execution plan of the original query looked like.


Thanks Grant. Me too, there are over 70 reads of the lookup.


Yeah, I'm curious how the optimizer is resolving those. With a much smaller number I would expect it to figure out the JOIN on its own. With such a big number... it could be fun.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99553 Visits: 33014
ChrisM@Work (1/24/2014)
IgorMi, you can use http://extras.sqlservercentral.com/prettifier/prettifier.aspx.

I tend not to. If I'm about to spend a few minutes reading and understanding a chunk of code, reformatting it easily becomes part of the process.



Another option is SQL Prompt from Red Gate. Can't live without that tool.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search