Por empezar, como UPDATE está mal escrito. La sitaxis base es:
Luego, MySQL no admite que consultes y actualices la misma tabla en la misma consulta. Es por un tema de seguridad de procesos, pero la idea es que puedes terminar por actualizar el mismo campo que estás consultando, por lo que terminas cortando la rama sobre la que estás parado... cosa que sería un desastre.
En general, el problea que te aqueja es relativamente sencillo de resolver, pero yo le veo dos vías:
1) Un SP y usando un CURSOR, lo que permitiría realizar obtener la tabla ordenada y reinicar el contador por cada cambio de producto.
2) Usar una tabla TEMPORARY para ubicar las numeraciones y luego un simple UMPDATE con INNER JOIN permitiría hacer la tarea.
Pero... ¿para qué quieres renumerarla, si ya tienen un orden definido por el ingreso? Si es para que aparezca el numero listado, no necesitas ni ese campo, ni ningún otro. Lo puedes obtener simplemente en la misma consulta donde recuperas la lista.