Q:

How to Write query for MySQL

I have two tables as:  
mysql> select * from survey;
+-----------+-----------+----------+--------+-----------+
| survey_id | client_id | stage_id | by_ref | no_branch |
+-----------+-----------+----------+--------+-----------+
|         2 |        65 |       72 | P      | 15        |
|         3 |        67 |       72 | D      | 2         |
+-----------+-----------+----------+--------+-----------+
2 rows in set (0.07 sec)

mysql> select * from allcode where code_type="MARKETING_STAGES";
+------------------+---------+------+--------------------+
| code_type        | code_id | srno | code_name          |
+------------------+---------+------+--------------------+
| MARKETING_STAGES |      72 |    1 | Enquiry            |
| MARKETING_STAGES |      73 |    3 | Meeting            |
| MARKETING_STAGES |      74 |    4 | Presentation       |
| MARKETING_STAGES |      75 |    5 | Review / Follow up |
| MARKETING_STAGES |      76 |    6 | Negotiation        |
| MARKETING_STAGES |      77 |    7 | Order              |
| MARKETING_STAGES |      78 |    8 | Agreement          |
| MARKETING_STAGES |     162 |    9 | Complete           |
| MARKETING_STAGES |     163 |    2 | Tender             |
+------------------+---------+------+--------------------+
9 rows in set (0.04 sec)

I want to update stage_id of survey table to next value which will be fetch from allcode code_id.
Right now I have client_id i.e. 65 from survey table, and want to update stage_id to 163 ( i.e. Next code_id from allcode table on sorting based on srno )
What I have tried till is 
update survey as s 
set s.stage_id=
(select code_id from allcode 
where code_id > (select stage_id from (select * from survey where client_id=65 )as su) 
and code_type="MARKETING_STAGES"
limit 1) 
where client_id=65;

This query update stage_id of allcode to 73 and I want it to be updated to 163 (Depending on srno)

A:

I would use joins in the update to get the next code_id based on srno:
update survey s
inner join allcode a1 on s.stage_id=a1.code_id
inner join allcode a2 on a1.srno=a2.srno-1
set s.stage_id=a2.code_id
where a1.code_type='MARKETING_STAGES' and a2.code_type='MARKETING_STAGES' and s.client_id=65

I assumed that srno field increments by 1 without any gaps. The purpose of the 1st join is to get the current stage_id's srno. Then the 2nd join gets the stage_id for the next srno.

