RE: Optimize Update Statement

SSC-Dedicated

Points: 33109

January 24, 2014 at 3:21 am

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