0

I have two tables:

  ________________________  ________________________________
  | id  |  categoryName  |  | product  |  categories       |
  ------------------------  --------------------------------
  |  1  |  clothes       |  | coat     |  clothes,outdoor  |
  |  2  |  indoor        |  | slippers |  clothes,indoor   |
  |  3  |  PPE           |  | umbrella |  outdoor,ppe      |
  |  4  |  outdoor       |  |          |                   |
  ------------------------  --------------------------------

I need to replace the values in the categories field in the second table for the category IDs in the first table so the tables would end up looking like this:

  ________________________  ________________________________
  | id  |  categoryName  |  | product  |  categories       |
  ------------------------  --------------------------------
  |  1  |  clothes       |  | coat     |  1,4              |
  |  2  |  indoor        |  | slippers |  1,2              |
  |  3  |  PPE           |  | umbrella |  4,3              |
  |  4  |  outdoor       |  |          |                   |
  ------------------------  --------------------------------

Since I have so many to update I am trying to work out how to do it programatically, but am struggling with it.

Dan
  • 1,154
  • 1
  • 7
  • 14
  • 1
    Normalize the schema; Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|192.7434) – sticky bit Apr 21 '20 at 11:24
  • I am normalising the data, this is just one step in the process that I need to achieve – Dan Apr 21 '20 at 11:27
  • OK, let me be more precise: Normalize it to 1NF first. – sticky bit Apr 21 '20 at 11:32
  • Rather than focusing on getting the data structure the way it should be, can you actually help me achieve what I asked? – Dan Apr 21 '20 at 11:35
  • I already helped you. Normalize the schema and it's a relatively simple thing to replace the names with the IDs. Normalization to 1NF is the first step to solve your problem. – sticky bit Apr 21 '20 at 11:38
  • That's not helpful though - that's not what I am doing here. – Dan Apr 21 '20 at 11:39
  • Well, you did and are doing something wrong. You need to correct that mistake first, like it or not. You're trying to put the cart before the horse... – sticky bit Apr 21 '20 at 11:42
  • Ok, fair enough, I was hasty. Thanks for your help - I have done that now and solved the problem as you suggested. – Dan Apr 21 '20 at 12:01

0 Answers0