CODEWITHSUNDEEP
mysqlsqlsql-updatesubquery
Jan.J
Jan.J

Reputation: 3080

MySQL update value from the same table with count

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.

Upvotes: 8

Views: 9855

Answers (4)

Kathara
Kathara

Reputation: 1290

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:

  1. 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
  2. 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.

Upvotes: 0

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

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

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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 ;

Upvotes: 10

Jan.J
Jan.J

Reputation: 3080

I found working solution, but this is just workaround:

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

Upvotes: 3

Related Questions