MySQL update value from the same table with count

Question

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.

score 10 · Accepted Answer · answered Jan 03 '13 at 12:36

10

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 ;

answered Jan 03 '13 at 12:36

ypercubeᵀᴹ

113,259
19
174
235

This is working. Thanks. My solution is faster, but has also some limitations – Jan.J Jan 03 '13 at 13:41
Could you explain why you use <= in the JOIN statement? why not just =? – Kathara Nov 11 '21 at 09:05
Oh wait, I just realized this was in the original question... ignore my comment before... – Kathara Nov 11 '21 at 09:06
Yes, it's a "triangular" self-join, used to provide numbers from 1 to N. – ypercubeᵀᴹ Nov 11 '21 at 09:59

score 3 · Answer 2 · answered Jan 03 '13 at 12:30

3

I found working solution, but this is just workaround:

SET @code=0;
UPDATE patients SET patient_code = (SELECT @code:=@code+1 AS code)

answered Jan 03 '13 at 12:30

Jan.J

3,050
1
23
33

1

You may want to add `ORDER BY patient_id` (MySQL allows ORDER BY in an UPDATE without joins). – ypercubeᵀᴹ Jan 03 '13 at 13:42

Mariappan Subramanian · Answer 3 · 2013-01-03T12:41:11.550

1

Try this,

UPDATE patients p1 INNER JOIN
(
    SELECT COUNT(*) as count,patient_id
    FROM patients
    group by patient_id  
)p2 
SET p1.patient_code=p2.count
WHERE p2.patient_id <= p1.patient_id

SQL_LIVE_DEMO

edited Jan 03 '13 at 12:41

answered Jan 03 '13 at 12:36

Mariappan Subramanian

9,527
8
32
33

Kathara · Answer 4 · 2021-11-11T11:40:51.850

Thanks to Mari's answer I found a solution to my similar problem. But I wanted to add a bit of an explanation which for me at first wasn't too clear from his answer.

What I wanted to do would have been as simple as the following:

UPDATE my_comments AS c 
SET c.comment_responses = (
   SELECT COUNT(c1.*) FROM my_comments AS c1
   WHERE c.uid = c.parent_uid
);

Thanks to Mari I then found the solution on how to achieve this without running into the error You can't specify target table 'p1' for update in FROM clause:

UPDATE my_comments AS c
INNER JOIN (
    SELECT c1.parent_uid, COUNT(*) AS cnt
        FROM my_comments AS c1
        WHERE c1.parent_uid <> 0
        GROUP BY c1.parent_uid
) AS c2
SET c.comment_responses = c2.cnt
WHERE c2.parent_uid = c.uid;

My problems before getting to this solution were 2:

the parent_uid field doesn't always contain an id of a parent which is why I added the WHERE statement in the inner join
I didn't quite understand why I would need the GROUP BY until I executed the SELECT statement on it's own and the answer is: because COUNT groups the result and really counts everything. In order to prevent this behavior the GROUP BY is needed. In my case I didn't have to group it by uid though but the parent_uid to get the correct count. If I grouped it by uid the COUNT would always be 1 but the parent_uid existed multiple times in the result. I suggest you check the SELECT statement on it's own to check if it's the result you expect before you execute the full UPDATE statement.

MySQL update value from the same table with count

4 Answers4