SQL Server Update particular record in a table

Question

SQL Server Update particular record in a table

kalyan Dhanekula 0

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 outut should be

-- (123,'Lessthan8','compliant'),

-- (123,'greatherthan8','Missing')

Select * from #test

2 answers

Your answer

Answer 1

Viorel 121.3K

If the particular record can be identified by a simple condition, then use where:

update #test
set [status] = 'Missing'
where PID = 123 and Category = 'greatherthan8'

kalyan Dhanekula 0 Reputation points

2025-04-23T21:09:36.54+00:00

in this example 123. i created sample test table here. here i know it is 123 and i can update through hard coding

But i have very big table where i need to update for each PID.

Basically same member can be in 2 categories with 'compliant' status. in that case status needs to update to 'Missing' for 'greatherthan8' category.

Answer 2

Bruce (SqlWork.com) 74,936

you haven't defined what identifies a unique row, so you may need all columns:

update #test
set status = 'new value'
where PID = 123 and Category = 'greatherthan8' and status = 'compliant'

note: this is poor table design. a table should have a unique key.

Share via

SQL Server Update particular record in a table

2 answers

Your answer