Update and set value with max () + 1 problems

Question

I am trying to update a string and set the shipment_number parameter to max()+1 . The reason for this in the table already has auto_increment in another column named id. From the first request, I get max(shipment_number) , and in the next request iam is incremented with +1 . But I get an error message.

# 1093 - You cannot specify the target table 'commercial_sales_custpo_process' for updating in the FROM clause

Any pls help me with this request.

 $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 not available, just 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'