Question 20624 in Mysql Question posted in
The official MySQL documentation can be found here.

Regenerate all integers in a pk column from zero and keep previous value in another column – Mysql

I need to regenerate all primary key numbers from zero and keep previous amount in ex column but the query doesnt work :

update mh_product AS p,(select p.id AS oldid,row_number() OVER (order by p.id asc)-1 AS newid from p) AS bp set p.id=bp.newid,p.ex=bp.oldid

I have this table

+-----------+----------+
| id        | ex       |
+-----------+----------+
| 24        | 0        |
| 25        | 0        |
| 27        | 0        |
| 29        | 0        |
| 30        | 0        |
+-----------+----------+

and need to convert to this :

+-----------+----------+
| id        | ex       |
+-----------+----------+
| 0         | 24       |
| 1         | 25       |
| 2         | 27       |
| 3         | 29       |
| 4         | 30       |
+-----------+----------+
Tags:

2

Answers


  1. 0

    I actually suggest considering not doing this update, the reason being that if your data changes again in the future, you might be forced to run the update multiple times.

    Instead, consider just querying with a select to view the data you want:

    SELECT
    ROW_NUMBER() OVER (ORDER BY id) - 1 AS id,
    id AS ex
FROM mh_product
ORDER BY id;
    Login or Signup to reply.
  2. 0

    The query you provided is almost correct, but there is a small syntax error that needs to be corrected. The table alias p is being used inside the subquery select p.id…, but p has not been defined yet. Instead, you should use the subquery’s table alias bp to reference the id column:
    Please use this as the solution.

    UPDATE mh_product AS p, (
        SELECT p.id AS oldid, 
               ROW_NUMBER() OVER (ORDER BY p.id ASC)-1 AS newid
          FROM mh_product AS p) AS bp
   SET p.id = bp.newid, 
       p.ex = bp.oldid;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search