0

I have a table with columns RECID,regionID, areaID ,UserID ,AppNo, NextAppNo(Accepts null)

and data is as follows (NextApp is not null in the first row only):

1,102,Ar-1,xyz,1,1
2,102,Ar-1,qwe,2
3,102,Ar-1,rtr,3
4,102,Ar-1,cvcv,4

Now i want to update and select in same query e.g Now i am doing this

update tbl set NextAppNo = randomnum
select UserID, AppNo from tbl where AppNo =(select NextAppNo from tbl where AppNo = 1)

Can this be done in one query?

P.S

i tried this

SELECT NextApproverNo FROM FINAL TABLE (update tbl set NextApproverNo = '2' where appNO=1)

and getting the result but when i do this 

Select userName from tbl where appNO = (SELECT NextApproverNo FROM FINAL TABLE (update tbl set NextApproverNo = '2' where appNO=1)  FETCH FIRST 1 ROW ONLY)

i got error

i get following error when i used join on this error as AN SQL DATA CHANGE STATEMENT WITHIN A FROM CLAUSE IS NOT ALLOWED IN THE CONTEXT IN WHICH IT WAS SPECIFIED

Haider
  • 615
  • 1
  • 16
  • 38
  • There are many duplicates. E.g.: http://stackoverflow.com/q/2934369/224704, http://stackoverflow.com/q/17583747/224704, http://stackoverflow.com/q/1244973/224704 – Disillusioned Jan 14 '14 at 18:59
  • @peter.petrov I'm not commenting on the possibility of what OP is asking. I'm commenting on the fact that there are many such questions on this forum already. OP should check those for an answer rather than creating another question which simply results in answers being spread in multiple places. – Disillusioned Jan 14 '14 at 19:06
  • @CraigYoung Right, OK. – peter.petrov Jan 14 '14 at 19:13
  • Why would you want to update with a random number? Why are you trying to do an UPDATE and a SELECT in the same statement? As was suggested on your other post, it helps to carefully explain in English what your goals are (and why) for us to give you the best advice. Also, what OS is your DB2 server running on? – WarrenT Jan 15 '14 at 21:17
  • i am not updating it with random number, as i am using ibm product it is creating table in db automatically so i dint want to create a stored procedure thats why i was doing it in query. Window 7 – Haider Jan 16 '14 at 16:55
  • 1
    @CraigYoung none of those questions are for DB2, and answers for this type of question are platform dependent. – WarrenT Jan 18 '14 at 01:16
  • @WarrenT (1) I provided a link to similar questions from the first screen-full of results after a simple search. There are **many** more, and checking them might provide the answer. (2) One of the reasons for trying to eliminate duplicates is to avoid having so many places to look. (3) Yes, in some cases a particular flavour of SQL may have a different answer; but since SQL is a standard language, in most cases it is better to to answer the query type questions for all flavours together. (4) If this truly is not a duplicate (e.g. based on `FINAL TABLE` syntax), then by all means it can stay. – Disillusioned Jan 18 '14 at 05:47
  • @Haider Have you tried `SELECT UserName FROM FINAL TABLE (update tbl set NextApproverNo = '2' where appNO=1)`. I'm not familiar with FINAL TABLE, but a different question implies it should work like that. If not, I may be able to offer some alternative suggestions. – Disillusioned Jan 18 '14 at 05:51

0 Answers0