CODEWITHSUNDEEP
mysqlsqlsql-updatecircular-dependency
Christophe Deliens
Christophe Deliens

Reputation: 570

MySQL update query that uses aggregation subquery on the same table

I have this table named "TableA" which contains "customer_id" and "name" fields. I added a new field "ref". Now I want to update that table so that the "ref" field is set to 1001 for the first item of customer_id=1, 1002 for the 2nd item of customer_id=1, etc. and again 1001 for the first item of customer_id=2, etc.

Of course, I've got the that-was-to-be-expected mysql error message "You can't specify target table 'TableA' for update in FROM clause".

Also tried using the (SELECT next_ref FROM (SELECT ... WHERE A_sub.customer_id = A.customer_id) AS A_sub), but then MySQL says that "A.customer_id" isn't found.

Here's the query:

UPDATE TableA A
SET A.ref = (
    SELECT COALESCE(MAX(A_sub.ref) +1, 1001)
    FROM TableA A_sub
    WHERE A_sub.`customer_id` = A.`customer_id`
)
WHERE o.ref IS NULL;

Question is: can this be done in one query or do I have to use a temporary table? Thanks for the help!

Upvotes: 0

Views: 183

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Yes. You need to use update/join:

UPDATE TableA A join
       (SELECT A_sub.`customer_id`, COALESCE(MAX(A_sub.ref) +1, 1001) as newref
        FROM TableA A_sub
        GROUP BY A_sub.`customer_id`
       ) A_Sub
       on A_sub.`customer_id` = A.`customer_id`
  SET A.ref = newref
  WHERE a.ref IS NULL;

EDIT:

If you want an auto-incremented id, I would suggest that you define one as such. If you have to put one in, then you can do:

UPDATE TableA A cross join
       (SELECT @rn := coalesce(MAX(A_sub.ref) + 1, 1001) as maxref
        FROM TableA A_sub
       ) vars
  SET A.ref = (@rn := @rn + 1)
  WHERE a.ref IS NULL;

Upvotes: 0

Related Questions