-1

Is there a way to replace all 0s with a null for all the columns of a table ? I know how to do it for single column, I am searching for something like dynamic query for all the columns.

Shadow
  • 21
  • 5

1 Answers1

2

This answers the original version of the question.

You can write one update query, but you need to list all columns:

update t
    set col1 = nullif(col1, 0),
        col2 = nullif(col2, 0),
        . . . ;

You might want to add:

where col1 = 0 or col2 = 0 or . . .

To limit the number of rows that are processed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This can work, thanks but doesnt answer my question. I was looking for some dynamic way to execute for the whole table with 50+ columns and similarly for multiple tables – Shadow Jun 15 '21 at 20:32
  • @Shadow . . . At least part of your question was on how to avoid multiple `update`s. – Gordon Linoff Jun 15 '21 at 23:56
  • Yes that is correct, let me update my question – Shadow Jun 17 '21 at 00:44
  • @Shadow . . . Better you should ask a *new* question rather than editing one that has already been answered. – Gordon Linoff Jun 17 '21 at 00:45
  • I accept that the previous version was ambiguous, I have clearified it now. – Shadow Jun 17 '21 at 00:47
  • @Shadow you can copy paste the column names 50 times manually. Probably will be faster than creating a dynamic query. I think this answer is proper for the question, it could help a future person searching for it. If you need help creating a dynamic query, you should create a question for "How can you create a dynamic query to replace all 0's with nulls on all columns of a table", and put the code that you've created so far there, so people can help you. – Wadih M. Jun 17 '21 at 01:04
  • Copy pasting 50 columns for a single table and then doing it for all the tables is not the solution I asked. – Shadow Jun 18 '21 at 09:19