Shorten SQL update query
Is there a way to shorten this update query? I'm new to this and not sure how can I further shorten it
UPDATE [Tbl_SS_Utilization(F03)_Copy1]
SET [WW0] = 0
WHERE [WW0] IS NULL
UPDATE [Tbl_SS_Utilization(F03)_Copy1]
SET [WW0+1] = 0
WHERE [WW0+1] IS NULL
UPDATE [Tbl_SS_Utilization(F03)_Copy1]
SET [WW0+2] = 0
WHERE [WW0+2] IS NULL
UPDATE [Tbl_SS_Utilization(F03)_Copy1]
SET [WW0+3] = 0
WHERE [WW0+3] IS NULL
UPDATE [Tbl_SS_Utilization(F03)_Copy1]
SET [WW0+4] = 0
WHERE [WW0+4] IS NULL
UPDATE [Tbl_SS_Utilization(F03)_Copy1]
SET [WW0+5] = 0
WHERE [WW0+5] IS NULL
I don't know what RDMBS you're using, but your posted SQL looks like T-SQL (for Microsoft SQL Server or Sybase), in that case then this single UPDATE
statement should do the trick:
UPDATE
[Tbl_SS_Utilization(F03)_Copy1]
SET
[WW0] = CASE WHEN [WW0] IS NULL THEN 0 ELSE [WW0] END,
[WW0+1] = CASE WHEN [WW0+1] IS NULL THEN 0 ELSE [WW0+1] END,
[WW0+2] = CASE WHEN [WW0+2] IS NULL THEN 0 ELSE [WW0+2] END,
[WW0+3] = CASE WHEN [WW0+3] IS NULL THEN 0 ELSE [WW0+3] END,
[WW0+4] = CASE WHEN [WW0+4] IS NULL THEN 0 ELSE [WW0+4] END,
[WW0+5] = CASE WHEN [WW0+5] IS NULL THEN 0 ELSE [WW0+5] END
WHERE
[WW0] IS NULL OR
[WW0+1] IS NULL OR
[WW0+2] IS NULL OR
[WW0+3] IS NULL OR
[WW0+4] IS NULL OR
[WW0+5] IS NULL
Or with COALESCE
(or ISNULL
if you're feeling brave):
UPDATE
[Tbl_SS_Utilization(F03)_Copy1]
SET
[WW0] = COALESCE( [WW0] , 0 ),
[WW0+1] = COALESCE( [WW0+1], 0 ),
[WW0+2] = COALESCE( [WW0+2], 0 ),
[WW0+3] = COALESCE( [WW0+3], 0 ),
[WW0+4] = COALESCE( [WW0+4], 0 ),
[WW0+5] = COALESCE( [WW0+5], 0 )
WHERE
[WW0] IS NULL OR
[WW0+1] IS NULL OR
[WW0+2] IS NULL OR
[WW0+3] IS NULL OR
[WW0+4] IS NULL OR
[WW0+5] IS NULL