0

I have two tables. One is 'business' structured like so:

business_id | name | linkto_category_id | business_category_description

and another one 'business_category' that holds the categories list

business_category_id | business_category_description

Now I would like to select all entries in 'business' with the corresponding 'linkto_category_id' that equals the 'business_category_id' in the 'business_category' table and then insert the 'business_category_description' into the 'business' table.

This is what I have so far...

INSERT INTO business(business_category_description)
(SELECT business_category.business_category_description, business_category.business_category_id, business.linkto_category_id
FROM business
INNER JOIN business_category
ON business.linkto_category_id=business_category.business_category_id);
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Benjamin Cuslidge
  • 68
  • 1
  • 2
  • 10
  • I think you might want an update rather than an insert. Insert will be adding a new row (or rows) with just the Business_category_description populated. – Brody Sep 28 '16 at 00:14

2 Answers2

0

Generally you shouldn't replicate data you can access with an inner join into a another table. You use the inner join to get the description from the category each time you get the business.

Normalising your data is usually done to prevent things getting out of date and to minimise the amount of data you are storing.

Using the linkto_category_id allows you to retrieve the description every time you need it without having to store a big identical text field in each business row.

Brody
  • 2,074
  • 1
  • 18
  • 23
  • Yes I understand this. I am doing this because I am exporting this data from an old database and using this to import into a custom post type in WordPress. – Benjamin Cuslidge Sep 28 '16 at 16:16
0

As Brody commented, it was an update that was needed.

Used top answer from here: How can I do an UPDATE statement with JOIN in SQL?

Community
  • 1
  • 1
Benjamin Cuslidge
  • 68
  • 1
  • 2
  • 10