Solved

sql udpate

Posted on 2011-03-03
4
766 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:ayha1999
4 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
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
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
Comment Utility
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
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
 
LVL 7

Author Closing Comment

by:ayha1999
Comment Utility
thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now