MySQL update value from the same table with count

mysql, sql, sql-update, subquery

Solution

UPDATE
    patients AS p
  JOIN
    ( SELECT 
          p1.patient_id
        , COUNT(*) AS cnt 
      FROM 
          patients AS p1
        JOIN 
          patients AS p2
            ON p2.patient_id <= p1.patient_id 
      GROUP BY 
          p1.patient_id
    ) AS g
    ON g.patient_id = p.patient_id
SET 
    p.patient_code = g.cnt ;

Problem

What I want to do is to set every patient its unique patient code which starts with 1 and it's not based on row id. Id only specifies order. Something like this: ``` patient_id patient_code 2 1 3 2 4 3 ``` This is my query: ``` UPDATE patients p1 SET p1.patient_code = ( SELECT COUNT( * ) FROM patients p2 WHERE p2.patient_id <= p1.patient_id ) ``` But it is throwing error: ``` #1093 - You can't specify target table 'p1' for update in FROM clause ``` I found this thread: Mysql error 1093 - Can't specify target table for update in FROM clause. But I don't know how to apply approved answer this to work with subquery WHERE which is necessary for COUNT.

Original source

Related problems