Optimize Update Statement

Question

Post reply

Optimize Update Statement

wit_jp2001

SSC Veteran

Points: 262
More actions
January 23, 2014 at 9:45 pm

#284779

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)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

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 SSC Guru Points: 186127 More actions · Answer 6

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 8

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 9

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[/url]. 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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 10

Grant Fritchey (1/24/2014)
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[/url]. Can't live without that tool.

Yes, I saw it as well. It helps a lot.

Thanks to both!

Igor Micev,My blog: www.igormicev.com

wit_jp2001 SSC Veteran Points: 262 More actions · Answer 11

Thanks,

I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. When this system prints a pick ticket it has to find the heaviest sku to determine box size from the start.

But this statement plus a few others just eat away at the time to do the actual run of the code.

Thanks again.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

wit_jp2001 (1/24/2014)
Thanks,
I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. When this system prints a pick ticket it has to find the heaviest sku to determine box size from the start.
But this statement plus a few others just eat away at the time to do the actual run of the code.
Thanks again.

I'm not sure what it's meant to do. There's no mention of weight anywhere, and it updates tons of columns, not just the 1st SKU. This description doesn't sit well with the evidence.

^{“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

wit_jp2001 SSC Veteran Points: 262 More actions · Answer 13

The SKU1 is updated with the heaviest sku in the list of SKU's.

The heaviest SKU is predetermined in SKUVAL, the weights are hidden.

So essentially it looks through the list and switches the heaviest with the first sku.

Thanks

wit_jp2001 SSC Veteran Points: 262 More actions · Answer 14

wit_jp2001

SSC Veteran

Points: 262

January 24, 2014 at 8:21 pm

#1683306

Chris - Thanks works great.