CODEWITHSUNDEEP
mysqlsql
Krule
Krule

Reputation: 6476

Update table by executing query on itself

I would like to update table based on a query selecting from itself. Starting from a table like this:

__________________________
| id | uid |  uid_seq_no |
--------------------------
| 1  | a   |     NULL    |
| 2  | a   |     NULL    |
| 3  | b   |     NULL    |
| 4  | a   |     NULL    |
| 5  | b   |     NULL    |
| 6  | b   |     NULL    |
| 7  | a   |     NULL    |
| 8  | c   |     NULL    |
--------------------------

I would like to update uid_seq_no to a sequence number of row scoped to uid, so that end result would be:

__________________________
| id | uid |  uid_seq_no |
--------------------------
| 1  | a   |       1     |
| 2  | a   |       2     |
| 3  | b   |       1     |
| 4  | a   |       3     |
| 5  | b   |       2     |
| 6  | b   |       3     |
| 7  | a   |       4     |
| 8  | c   |       1     |
--------------------------

I have attempted to perform a query as follows:

UPDATE keySeq a
  SET uid_seq_no=(
    SELECT IFNULL(uid_seq_no,0)+1 FROM keySeq b
      WHERE a.uid = b.uid AND uid_seq_no IS NOT NULL
      ORDER BY id
      LIMIT 1
    );

But I get: Table 'a' is specified twice, both as a target for 'UPDATE' and as a separate source for data.

I also attempted to execute it like:

UPDATE keySeq a
  SET uid_seq_no=(
    SELECT n FROM (
      SELECT IFNULL(uid_seq_no,0)+1 AS n FROM keySeq b
        WHERE a.uid = b.uid AND uid_seq_no IS NOT NULL
        ORDER BY id
        LIMIT 1
      ) AS T
    )

But I get Unknown column 'a.uid' in 'where clause'. Probably because sub-subquery has no access to query scope.

Now I am out of ideas.

Sample table: http://sqlfiddle.com/#!9/e3f3b6/1

Upvotes: 1

Views: 201

Answers (3)

Zorkolot
Zorkolot

Reputation: 2027

You can use the function rownumber. This is partitioned by uid as shown below. A select would be like so:

SELECT id, uid
      ,ROW_NUMBER() OVER (PARTITION BY uid ORDER BY id, uid ASC) [uid_seq_no]
  FROM keySeq
ORDER BY id

To use the select in an update statement, you can use a column alias to disambiguate for the update:

UPDATE keySeq
   SET uid_seq_no = dT.uid_seq_no2

  FROM ( SELECT id [id2] --I use column alias here and for uid_seq_no2
        ,uid
        , ROW_NUMBER() OVER (PARTITION BY uid ORDER BY id, uid ASC) [uid_seq_no2]
          FROM keySeq K1
       ) AS dT

 WHERE id = dT.id2

Here is the sqlfiddle I used to test this: http://sqlfiddle.com/#!6/c5bae/1

Upvotes: 0

SmartDumb
SmartDumb

Reputation: 358

Alternative for setting rank value for update:

SELECT 
id
, uid
, uid_seq_no
, ( 
  CASE uid
  WHEN @vUid
  THEN @vRank := @vRank + 1 
  ELSE @vRank := 1 AND @vUid := uid END
) + 1 AS rank

FROM keySeq,
(SELECT @vRank := 0, @vUid := '') var
ORDER BY uid, id

Upvotes: 0

Bijan Ghasemi
Bijan Ghasemi

Reputation: 296

you can using this query:

UPDATE keySeq
left join  (
  select a.id, (SELECT count(1) + 1 FROM keySeq b 
                     where b.uid = a.uid and  b.id<a.id) Rank
  from keySeq a
) xQ on xQ.Id=keySeq.id
SET keySeq.uid_seq_no=xQ.Rank;

Upvotes: 2

Related Questions