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

Question

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 .

Khan Abrar Ahmed · 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 dodiya · Answer

thank you @Abrar and @Ankur.@Ankur i'm new to stored procedure so unable to get it fully . thanks for your time

Ankur Jain · Answer

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

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