Update particular record in a table

Question

Update particular record in a table

dhanekulakalyan

Default port

Points: 1468
More actions
April 23, 2025 at 7:29 pm

#4583169

I need to update greatherthan8 (category) record to Missing (status) if the same member is in different (category) but having same (status) and that also only when they are compliant.
for example 123 PID i have both lessthan8 and greaterthan8 (category) but having same (status) that is compliant so for this member 123 for greaterthan8 (category) record i need update (status) to missing.
for example 456 PID i have both lessthan8 and greaterthan8 (category) but having same (status) that is Missing so for this member 456 both (status) is missing we dont need to update any record.
for example 789,999 PID i have both lessthan8 and greaterthan8 (category) but having different (status) so we dont need to update any record.
for example 11,111,22,222 PID i have i have only one (status) so we dont need to update any record.
So my ask is for any member if i have different (category) but same (status) that too having compliant , i need to update greatherthan8 record with missing in (status).
how can i achieve this.
DROP TABLE if exists #test
create table #test
(
PID int,
Category varchar(100),
status varchar(10)
)
Insert into #test
values (123,'Lessthan8','compliant'),
(123,'greatherthan8','compliant'),
(456,'Lessthan8','Missing'),
(456,'greatherthan8','Missing'),
(789,'Lessthan8','compliant'),
(789,'greatherthan8','Missing'),
(999,'Lessthan8','Missing'),
(999,'greatherthan8','compliant'),
(11,'Lessthan8','Missing'),
(111,'greatherthan8','Missing'),
(22,'Lessthan8','compliant'),
(222,'greatherthan8','compliant')
--so output should be
-- (123,'Lessthan8','compliant'),
-- (123,'greatherthan8','Missing')
Select * from #test

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18895 More actions · Answer 1

Untested:

WITH Grps
AS
(
SELECT PID, Category, [status]
,MAX(IIF(Category <> 'greatherthan8', 1, 0)) OVER (PARTITION BY PID) AS LTExists
FROM #test
WHERE [status] = 'compliant'
)
UPDATE Grps
SET [status] = 'Missing'
WHERE Category = 'greatherthan8'
AND LTExists = 1;

select * from #test;

dhanekulakalyan Default port Points: 1468 More actions · Answer 2

Thank you, will take a look and update here if not working.

Steve Collins SSCrazy Points: 2197 More actions · Answer 3

Afaik there are only two possible values for the Category column: { 'Lessthan8', 'greatherthan8' }. If that's so the query could rely on EXISTS to handle any duplication

update t
set status='Missing'
from #test t
where t.Category='greatherthan8'
      and t.status='compliant'
      and exists(select 1
                 from #test tt
                 where tt.PID=t.PID
                       and tt.status=t.status
                       and tt.Category='Lessthan8');

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

dhanekulakalyan Default port Points: 1468 More actions · Answer 4

--I Have a similar scenario but with dates.

--I need to update Final_Flag Column to 1 or 0 based on categories and dates available. Please see examples below which are the same data.

--how can i achieve this.

--Scenario1: Same member can have category at row level and which ever category have most recent date the final_flag should popualate 1. 123 member 'lessthan8' category has date greater than 'greaterthan8' so 'lessthan8' categry record should populate 1 in final_flag column.

--Scenario1: Same member can have category at row level and which ever category have most recent date the final_flag should popualate 1. 456 member 'greaterthan8' category has date greater than 'lessthan8' so 'greaterthan8' categry record should populate 1 in final_flag column.

--Scenario2: Same member can have category at row level and for both categories date is same then keep 'greaterthan8' category to 1 for that record. 567 member 'greaterthan8' category record should populate 1 in final_flag column.

--Scenario3: Same member can have category at row level and one of the category can have null then which ever category have the date will have 1 for that record. 678 member 'Lessthan8' category record should populate 1 in final_flag column.

--Scenario4: Same member can have only one of the category and that record will be 1. 888 and 999 member which ever record is available that row will populate 1 in final_flag column.

DROP TABLE if exists #test

create table #test

(

PID int,Category varchar(100),Date_1 datetime,Date_2 datetime,Final_Flag varchar(10)

)

DROP TABLE if exists #test

create table #test

(

PID int,Category varchar(100),Sub_Category varchar(100),Date_1 datetime,Date_2 datetime,Final_Flag varchar(10)

)

--input data

Insert into #test (PID,Category,Sub_Category,Date_1,Date_2,Final_Flag)

values (123,'Category-A','Lessthan8','04/27/2025',Null,0),

(123,'Category-A','greatherthan8',Null,'04/25/2025',0),

(456,'Category-A','Lessthan8','04/20/2025',Null,0),

(456,'Category-A','greatherthan8',Null,'04/22/2025',0),

(567,'Category-A','Lessthan8','04/15/2025',Null,0),

(567,'Category-A','greatherthan8',Null,'04/15/2025',0),

(678,'Category-A','Lessthan8','04/15/2025',Null,0),

(678,'Category-A','greatherthan8',Null,Null,0),

(888,'Category-A','Lessthan8','04/12/2025',Null,0),

(999,'Category-A','greatherthan8',Null,'04/09/2025',0)

Select * from #test order by 1,2

--Expected OutPut data

(123,'Category-A','Lessthan8','04/27/2025',Null,1),

(123,'Category-A','greatherthan8',Null,'04/25/2025',0),

(456,'Category-A','Lessthan8','04/15/2025',Null,0),

(456,'Category-A','greatherthan8',Null,'04/22/2025',1),

(567,'Category-A','Lessthan8','04/15/2025',Null,1),

(567,'Category-A','greatherthan8',Null,'04/15/2025',0),

(678,'Category-A','Lessthan8','04/15/2025',Null,1),

(678,'Category-A','greatherthan8',Null,Null,0),

(888,'Category-A','Lessthan8','04/12/2025',Null,1),

(999,'Category-A','greatherthan8',Null,'04/09/2025',1)

This reply was modified 2 weeks, 3 days ago by dhanekulakalyan.
This reply was modified 2 weeks, 3 days ago by dhanekulakalyan. Reason: changed previous category column to sub_category and added category so there can be Main category assuming that may help in building query

Mike01 SSChampion Points: 11413 More actions · Answer 5

Did you try Lag/Lead??

select PID, Sub_Category, Date_1, Date_2,
Case when Date_1 >= NextDate or NextDate is null then 1
when Date_2 > LastDate then 1
else 0
end as FinalFlag
from (
Select *, lead(Date_2,1,0) over (partition by PID order by category, sub_category desc) as NextDate
,lag(Date_1,1,0) over (partition by PID order by category, sub_category desc) as LastDate
from #test
) v

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lisa Slater Nicholls Hall of Fame Points: 3461 More actions · Answer 6

> Afaik there are only two possible values for the Category colum

AFAICS you are right -- but you'd expect there would be a 3rd value, equals8???