Question 63616 in PhpMyAdmin Question posted in
The official documentation can be found here.

Replace index on some items in SQL table (phpMyAdmin)

I’m sure this is a bit dumb, but I’m very very beginner in MySQL so here I go.

I have a table Items with id (primary key), category_id (index) and some more columns. One of the categories (category_id = 5) is not needed anymore, and I would like to remove it and make all the items in category_id = 5 to become category_id = 7.

Find and replace won’t give the possibility of altering keys, so I’m not sure about how to proceed with that.

I can go to category_id and remove the number 5, but then all the items that used to have category_id = 5 will become null, right?

Tags:

2

Answers


  1. 0

    You should not do this. Changing the index numbers will affect the referential integrity of your other tables and is not a great idea in general as a result. Even if you manually change the values, it’s a bad practice and your database can scale enough to increase the index values rather than needing to compress them manually.

    If you insist on doing this, you can use phpMyAdmin’s search and replace feature to find and change the values in each table. I’m not sure what you mean by saying that find and replace won’t change the keys, you’ll just have to search through each table that could include the key and update it through phpMyAdmin.

    Login or Signup to reply.
  2. 0

    Sorry, I’m not sure about your explanation, but if all what you need is moving all items from category 5 to 7 you can use next simple query:

    UPDATE items SET category_id = 7 WHERE category_id = 5;

    sql online editor

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search