update and set value with max()+1 problems

Question

I am trying to update a row and set the shipment_number with max()+1. The reason for this is the table already has a auto_increment on another column named id. From the first query i'm getting the max(shipment_number) and in the next query iam incrementing with +1. But iam getting an error..

#1093 - You can't specify target table 'commercial_sales_custpo_process' for update in FROM clause

Anyone pls help me with this query.

$max = "SELECT MAX(shipment_number) FROM commercial_sales_custpo_process WHERE 
tender_id='$tender_id' AND id='$id'";

$query1="UPDATE commercial_sales_custpo_process set shipment_number = ($max+1) WHERE 
tender_id='$tender_id' AND id='$id'";
mysql_query($query1) or die ("Error in query: $query1");

Answer 1

If Auto_Increment is not available , simply use UPDATE..JOIN :

UPDATE commercial_sales_custpo_process t
CROSS JOIN (SELECT MAX(shimpent_number) + 1 as max_ship 
            FROM commercial_sales_custpo_process) s
 SET t.shipment_number = s.max_ship
WHERE t.tender_id='$tender_id' AND t.id='$id'