CODEWITHSUNDEEP
sqlsql-servert-sql
Soatl
Soatl

Reputation: 10592

SQL Update Statement with a Case with Select Inside

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?

Upvotes: 3

Views: 19744

Answers (4)

J0e3gan
J0e3gan

Reputation: 8938

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.

Upvotes: 3

sam yi
sam yi

Reputation: 4934

Does table1 only hold 1 row? How come you need a table for that? Have you considered using a view?

SELECT COALESCE(MAX(column1),0) AS MaxValue,
  COALESCE(MIN(column1),0) AS MinValue
FROM dbo.Table2

Also, why not coalesce the max as well.. just in case.

If you must use a table...

UPDATE dbo.Table1
SET MinValue = t.[min],
  MaxValue = t.[max]
FROM (SELECT MIN(column1) [min], MAX(column1) [max] FROM dbo.Table2) t

Upvotes: 0

Luc M
Luc M

Reputation: 17314

Why not

UPDATE Table1 SET MaxValue = (SELECT MAX(column1) FROM Table2), 
                 MinValue = COALESCE( SELECT MIN(column1) FROM Table2, '0' )
;

Upvotes: 1

John Woo
John Woo

Reputation: 263723

How about this?

UPDATE a
SET    a.MaxValue = b.max_val,
       a.MinValue = COALESCE(b.min_val, 0)
FROM   Table1 a
       CROSS JOIN 
       (
          SELECT MAX(column1) max_val, 
                 MIN(column1) min_val 
          FROM   Table2
       ) b

Upvotes: 6

Related Questions