Optimize Update Statement

  • 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)

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • Chris - Thanks works great.

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

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