SQL Update Statement with a Case with Select Inside

sql, sql-server, t-sql

Solution

Here are some immediately obvious adjustments that you need to make:

UPDATE Table1 SET MaxValue = (
       SELECT MAX(column1) FROM Table2),
       MinValue = (CASE
           WHEN (SELECT MIN(column1) FROM Table2) IS NOT NULL -- subquery in parentheses per John Gibb's comment and IS NOT NULL rather than <> NULL
           THEN (SELECT MIN(column1) FROM Table2) -- subquery in parentheses per John Gibb's comment
           ELSE 0 -- 0 rather than '0'
       END)

Otherwise, you are effectively coalescing with a `CASE`: I would use `COALESCE` instead.

Problem

I want to run a mass update statement that selects the min and max of a column. The issue is that there are multiple columns with null values. If `NULL` then I want to set the `MinValue` to `0`. I have the following statement, but I am getting errors with the `SELECT` and `ELSE`: ``` UPDATE Table1 SET MaxValue = ( SELECT MAX(column1) FROM Table2), MinValue = (CASE WHEN SELECT MIN(column1) FROM Table2 <> NULL THEN SELECT MIN(column1) FROM Table2 ELSE '0' END) ``` What am I missing?

Original source