CODEWITHSUNDEEP
sqlsql-server
Passiontolearn
Passiontolearn

Reputation: 103

To update a column in a table based on a existing data in a column using group by

I am new to SQL Server, kindly help

I have a table:

Group   id     DEPT     newadmission 
--------------------------------------
 A      001     CS       Y
 A      002     ECE      
 A      003     MC     
 B      561     CS  
 B      781     IT
 C      789     CS       Y
 C      003     CS

If a group has a new admission the entire group should get that value.

If there is no new admission it can be as it is.

How can I achieve that?

Desired output

Group   id      DEPT    newadmission 
------------------------------------
 A      001     CS          Y
 A      002     ECE         Y   
 A      003     MC          Y  
 B      561     CS    
 B      781     IT
 C      789     CS          Y
 C      003     CS          Y

Upvotes: 0

Views: 57

Answers (2)

Saravana Kannadasan
Saravana Kannadasan

Reputation: 160

You can just a common table expression followed by an Update statement to get the desired output.

;WITH CTE AS
(
SELECT DISTINCT [Group] FROM
Table1 WHERE newadmission = 'Y'
)
UPDATE t
SET
t.newadmission = 'Y'
FROM 
Table1 t INNER JOIN CTE ON
t.[Group] = CTE.[Group]

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Use window functions:

with toupdate as (
      select t.*, max(newadmission) over (partition by group) as new_newadmission
      from t
     )
update toupdate
    set newadmission = new_newadmission
    where newadmission is null;

This assumes that newadmission is either NULL or Y, although it is easily adapted for other values.

Upvotes: 1

Related Questions