How can I use the max() function with the update statement?

Question

Currently I'm working on a project for class that requires us to create a front end for a mysql database. For learning purposes we're only using a single patient table without auto-increment implemented for the unique identifier (patient_id). We have to add new patients and to handle this we planned to have the C# front end to create a patient_id value of 0 and then have another statement to update this to be the max patient_id + 1. I'm aware this is not a practical/safe solution since it could cause errors but since it's only for testing and isn't linking with other tables I'm not too concerned.

I've attempted using basic queries to tackle this by setting patient_id = max(patient_ID) + 1 but this doesn't seem to work.

UPDATE patient
set patient_id = (select max(patient_id) + 1 from patient)
where patient_id = 0;

The output of this query says that I can't specify the target table 'patient' for update in FROM clause so I'm a little bit confused as to why it can't.

Answer 1

You can't use the same table for select and update but you are using a subquery for build a stmp table and avoid the issue:

  UPDATE patient
  SET patient_id = (SELECT t.max_id  
                    FROM
                        (SELECT MAX(patient_id) + 1 FROM patient)) t
  WHERE patient_id = 0;