Update column in table depending on table data

Question

I am working on a table on SQL where I need to fill in a new column depending on the value of 2 other columns in the table.

The rules are: - New column = 1 if Row id = 1 and the group id for that row has more rows with the same group id (See pic) - New column = 2 if Row id >= 2 - Anything else will make the new column 0

Table

This is my query to update the new column which works:

UPDATE t1 SET t1.New_Column = CASE WHEN t1.RowID >= 2 THEN 2 
                                    ELSE CASE WHEN t1.RowID = 1 and (SELECT count(*) FROM dbo.__test1 as t2 WHERE t2.GroupID = t1.GroupID) > 1 
                                    THEN 1 ELSE 0 END END   from dbo.__test1 as t1

The end table result(Correct):

I want to know if there is a better and more efficient way of updating the new column as I feel calling the table within the select to count the number of group id's doesn't seem right?

Thanks

score 0 · Answer 1 · answered Mar 22 '18 at 21:54

It will be faster to get the counts at the table level instead of row.

Removed one level of your CASE expression too - that probably won't change anything but it's a little cleaner to my eyes.

UPDATE t
SET t.NewColumn = CASE WHEN t2.RowID >=2 THEN 2
                       WHEN t2.RowID = 1 AND cnt > 1 THEN 1
                       ELSE 0 
                       END
FROM dbo.__test1 t
JOIN (SELECT GroupID, COUNT(*) cnt
      FROM dbo.__test1
      GROUP BY GroupID) t2 on t.GroupID = t2.GroupID

Update column in table depending on table data

1 Answers1