CODEWITHSUNDEEP
mysqlsql
Silverlan
Silverlan

Reputation: 2911

Self-referencing MySQL table in UPDATE-query

I have the following scenario:

3 tables are involved, 'patient', 'users' and 'center'. They have the following attributes and relationships:

'patient':

'users':

'center':

Patients aren't directly assigned to a center. Instead, they are assigned to a specific user, who in turn is assigned to one center.

The 'localid' of the patient is currently set to '-1' for all patients. I'm trying to come up with a SQL query which increments the 'localid' for all patients, but only within the center they are indirectly associated with through the user.

In essence, I want all patients to have a unique ID within their center, but it should start at 0 for each center.

This is the query I have tried, which I think is close to what I need:

UPDATE patient p1
SET p1.localid=(SELECT (max(p2.localid) +1)
                FROM patient p2
                WHERE (SELECT u1.cid
                       FROM users u1
                       WHERE u1.uid=p1.doc)=(SELECT u2.cid
                                             FROM users u2
                                             WHERE u2.uid=p2.doc));

This results in the sql error "You can't specify target table 'p1' for update in FROM clause" because 'p1' is referencing itself in the WHERE-clause.

I can easily solve this problem by using multiple SQL queries and a loop in PHP, but I was wondering if it was possible to solve this with just plain SQL?

Upvotes: 0

Views: 161

Answers (1)

Joe
Joe

Reputation: 15812

Instead of using subqueries, you can JOIN the table to itself using the criteria from your subquery and give it an alias. That should allow you to update the same table you're querying.

Something like...

UPDATE patient p1
SET p1.localid = p2.newlocalid
INNER JOIN (
    SELECT pid, MAX(localid) + 1 AS newlocalid
    FROM patient p2
    WHERE...
) p2 ON p1.pid = p2.pid

Upvotes: 1

Related Questions