Unknown User Unknown User - 1 year ago 66
SQL Question

How to condense multiple update statement - SQL

I have an update statement for 10 columns which is replacing values in each of the column with a where condition to that specific column.

Here's the code:

SET Col1 = REPLACE(Col1, '#DIV/0', NULL)
WHERE Col1 = '#DIV/0';

SET Col2 = REPLACE(Col2, '#DIV/0', NULL)
WHERE Col2 = '#DIV/0';

SET Col3 = REPLACE(Col3, '#DIV/0', NULL)
WHERE Col3 = '#DIV/0';

Like this I have it for 10 columns, it does the work but it doesn't look clear or professional.

I wanted to condense this code and make it look like a professional code.

Any suggestions is much appreciated.


Answer Source

I suggest you leave it as it is, with one change because statements such as REPLACE(Col1, '#DIV/0', NULL) do not make sense: calling REPLACE in sql server where any parameter is NULL always produces NULL, regardless if the text is found or not.

So best is this:

UPDATE Table1 SET Col1 = NULL WHERE Col1 = '#DIV/0';
UPDATE Table1 SET Col2 = NULL WHERE Col2 = '#DIV/0';
UPDATE Table1 SET Col3 = NULL WHERE Col3 = '#DIV/0';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download