sql udpate

I have two tables named issue and stock. I want to delete record from issue table and update stock table if only the partno from stock table exists in the issue table. how can write a single sql statment? Also how can write 'success' and ' fail' for delete and update respectively?

partno storeid    stock
p101    sh            100
p101    ws            50

issue
id  partno  qty  storeid
p  101      10    sh

thanks

ayha
LVL 7
ayha1999Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
You need two statements, One for UPDATE and another for DELETE. You can try like this. Where do you want to write 'Success' and 'Failure' messages?
Do you want to log it somewhere?
update s
   set s.stock = i.qty
  from stock s
  join issue i on s.storeid = i.storeid
delete from issue 

Open in new window

0
 
Aaron ShiloChief Database ArchitectCommented:
this should get you started

begin

delete from issue where partno  =101

update stock table set column = value
where partno = (select partno from deleted)

end
0
 
LowfatspreadCommented:
you can't have 1 sql statement do what you are asking...

the closest you could come is to put all the code required into a stored procedure
which you could the perform as 1 statement....

please be more specific about your requirements...

what does if only the partno from stock table exists in the issue table mean
and Also how can write 'success' and ' fail' for delete and update respectively?  
what do you mean by write and where do you want it published?
0
 
ayha1999Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.