check for column value and update value in that row in sql

varsha dodiya

12y1.5k

geeks,

plz help me with sql query

i have a table like this

p_name	qty	batchno	vendor	Odr flag
disprin	20	b5	A	ordered
crocin	5	d3	B	null
disprin	12	b6	B	ordered
(new insert)	2	g3	w	null

you see here disprin is indicating ordered from Odr flag

while crocin has null value in odr flag as not been ordered yet .

now i want when i insert another entry (for example disprin) then query checks if its Odr col has value 'Ordered' then update it as null in all the rows having P_name 'disprin' .

so on inserting disprin it should be like this

p_name	qty	batchno	vendor	Odr flag
disprin	20	b5	A	null
crocin	5	d3	B	null
disprin	12	b6	B	null
disprin	2	g3	w	null

help me guys. any help will be appreciated .

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Khan Abrar Ahmed12y ago
Accepted answer
Hi check this
IF((SELECT COUNT(1) FROM order WHERE [Odr flag]='Ordered' AND p_name='disprin')>0)
BEGIN
UPDATE ORDER SET [Odr flag]=NULL WHERE p_name='disprin'
END

--Add the insert query here

Please mark at answer if it help you.
varsha dodiya12y ago
thank you @Abrar and @Ankur.@Ankur i'm new to stored procedure so unable to get it fully . thanks for your time
Ankur Jain12y ago
Hi!

try this query, you can change parameter name as per your requirement...hope this will hep you...

declare @isOrdered nvarchar(50)
select @isOrdered=count(*) from [Order] where p_name='disprin' and orderflag ='Ordered'
if(@isOrdered>0)
begin
update [Order] set orderflag= null where p_name='disprin'
end
else
begin
print 'you can insert'
end