July 17, 2011 at 11:13 pm
Hi,
I want to put the below 2 queries in to stored procedure...
Based on the result from the select query(a),the c_number to be passed automatically to the update query(b) in IN condition ...Can anyone help me on how to do in SP??
(a)
select b.c_number from wuser a
inner join cliente b on a.fid=b.fid where a.userid='Amols' and status in ('OPEN')
(b)
update a set userid='Amolr' from wuser a inner join cliente b on a.fid = b.fid where b.c_number in ("c_number values from the above select query") and userid = 'Amols'
Eg : if select query returns with the following numbers
12345
34567
45672
Then the update query should be:
update a set userid='Amolr' from wuser a inner join cliente b on a.fid = b.fid where b.c_number in
(12345,34567,45672) and userid = 'Amols'
July 17, 2011 at 11:27 pm
Hi
You can use temp table and store your result of select statement in same and use it in update statement.
Viral
July 18, 2011 at 12:17 am
Can you pls give an example?
July 18, 2011 at 12:32 am
update a set userid='Amolr' from wuser a inner join cliente b on a.fid = b.fid where b.c_number =a.c_number and userid = 'Amols'
and status in('OPEN')
Try this...(it will work if am not wrong)
July 18, 2011 at 12:33 am
See whether this works:
--------------------------
update a set userid='Amolr' from wuser a inner join cliente b on a.fid = b.fid where b.c_number in
(
select b.c_number from wuser a
inner join cliente b on a.fid=b.fid where a.userid='Amols' and status in ('OPEN')
)
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
July 18, 2011 at 8:32 pm
Thanks 🙂 ..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply