CODEWITHSUNDEEP
mysqlsql
Mr.Manhattan
Mr.Manhattan

Reputation: 5504

MySQL - Update with counter based on subquery

I'm tring to make an update on a mysql table:

 PrimaryId | SecondaryId | Order
-----------+-------------+-------
     1     |      1      |   0
     2     |      1      |   0
     3     |      2      |   0
     4     |      3      |   0
     5     |      3      |   0
     6     |      3      |   0

to:

 PrimaryId | SecondaryId | Order
-----------+-------------+-------
     1     |      1      |   1
     2     |      1      |   2
     3     |      2      |   1
     4     |      3      |   1
     5     |      3      |   2
     6     |      3      |   3

on rows that have the same secondary id and an Order of 0. so far i tried to select the values to update in an subquery, and update the rows with max()+1

...sadly, this won't work since the select is not allowed to work on the same table as the update. is there a way to do that?

Upvotes: 1

Views: 110

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Try this way:

UPDATE Table1 t1
JOIN (
   SELECT `PrimaryId`,
          `SecondaryId`,
          (SELECT count(*)
           FROM Table1 t1
           WHERE t1.`SecondaryId` = t.`SecondaryId`
            AND t1.`PrimaryId` <= t.`PrimaryId`
           ) `Order`
   FROM Table1 t
) t2
ON t1.`PrimaryId` = t2.`PrimaryId`
SET t1.`Order` = t2.`Order`
;

Demo --> http://www.sqlfiddle.com/#!2/6f2102/1

Upvotes: 3

Related Questions