Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Optimize Update Statement Expand / Collapse
Author
Message
Posted Thursday, January 23, 2014 9:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 2:32 PM
Points: 8, 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)
Post #1534329
Posted Friday, January 24, 2014 1:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1534374
Posted Friday, January 24, 2014 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1534389
Posted Friday, January 24, 2014 2:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1534390
Posted Friday, January 24, 2014 3:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1534402
Posted Friday, January 24, 2014 3:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 3,084, Visits: 3,195
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,
SQL Server developer at Seavus
www.seavus.com
Post #1534407
Posted Friday, January 24, 2014 4:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1534423
Posted Friday, January 24, 2014 4:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1534424
Posted Friday, January 24, 2014 6:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1534454
Posted Friday, January 24, 2014 6:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1534456
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse