CODEWITHSUNDEEP
sql-server
Sri
Sri

Reputation: 2273

How to check the combination of values exists in a table with nullable column based on another column?

From the below table all the columns are varchar. ColumnA, ColumnB and ColumnC are non nullable columns. BoolCol is a Char(1) and ColumnD is nullable.

TableName: EXMP

ColumnA ColumnB ColumnC BoolCol ColumnD Count
First ABC HEL Y NULL 0
Second DEF HWR N 001a 0
First ABC IAF Y NULL 0
First ABC HEL Y NULL 0

I want to update the Count for each row by the identical values of ColumnA, ColumnB, ColumnC, and ColumnD.

Rules:

  1. If the BoolCol is 'Y', then the ColumnD must be NULL. So
  2. ColumnA, ColumnB, ColumnC, and BoolCol can only appear once. Once BoolCol is selected, then that specific combination can only happen once
  3. ColumnA, ColumnB, ColumnC can be repeated as long as the ColumnD is different including the NULL counts.

I am expecting the First and the last row Count should be 2 as all the column values are identical. What would be the Count column SQL should be for the each row?

Tried So far:

SELECT * FROM EXMP WHERE ColumnA = 'First' AND ColumnB = 'ABC' AND ColumnC = 'HEL' AND ((**<<ColumnD>>** IS NULL AND ColumnD IS NULL) OR (**<<ColumnD>>** IS NOT NULL AND ColumnD IS NOT NULL AND ColumnD = **<<ColumnD>>**))

Upvotes: 0

Views: 27

Answers (1)

Doug Coats
Doug Coats

Reputation: 7117

I think you might be over thinking it. This is a simple group by issue and an update join :)

Also I admit that maybe your rules are confusing. If this isnt working for you, please edit your desired results and maybe clarify your requirements.

Try this

UPDATE
    x
SET x.COUNT = y.CountOF
FROM 
    EXMP x
    INNER JOIN
        (
            SELECT
                ColumnA
                , ColumnB
                , ColumnC
                , BoolCol
                , ColumnD
                , COUNT(*) AS CountOF
            FROM
                EXMP
            GROUP BY
                ColumnA
                , ColumnB
                , ColumnC
                , BoolCol
                , ColumnD   
        ) y ON x.ColumnA=y.ColumnA
            AND x.ColumnB=y.ColumnB
            AND x.ColumnC=y.ColumnC
            AND x.BoolCol=y.BoolCol
            AND x.ColumnD=y.ColumnD

Upvotes: 1

Related Questions