0

I am trying to update multiple tables at the same time in SQL. I have a Products table which includes ProductName and ProductTypeID. I also have another table called ProductCategory which includes the ProductTypeID as the primary key and the full product type name. For example,

Products Table
ProductName  ProductTypeID  Price ...
Bananas      FR             0.79  ...
Milk         DR             2.19  ...
...          ...            ...   ...

and

ProductCategory Table
ProductType  CategoryName
FR           Fruit
DR           Dairy
...          ...

In my database, the fruit and vegetable sections are combining categories, so I want "Fruit" to become "Produce" and "FR" to become "PR". However, with the multiple table configuration, I am struggling to find a way to do this. I have been working with a stored procedure since that seems to be the simplest way to accomplish this.

The results would look like the following:

Products Table
ProductName  ProductTypeID  Price ...
Bananas      PR             0.79  ...
Milk         DR             2.19  ...
...          ...            ...   ...

and

ProductCategory Table
ProductType  CategoryName
Produce      PR
DR           Dairy
...          ...
  • *so I want "Fruit" to become "Produce" and "FR" to become "PR"* Don't understand this - where is "Produce" and "PR", what do you mean. Include the procedure you've got so far. It's not clear what you are asking. – Stu Apr 08 '21 at 19:45
  • You need to show sample data (ideally as DDL+DML), your desired results (as formatted text) and your attempt. As it stands it is unclear what you are asking. – Dale K Apr 08 '21 at 21:00
  • @DaleK sorry for the ambiguity, I provided some desired results now –  Apr 08 '21 at 21:47
  • 1
    @user15585845 still can't see your attempt. And are you sure you don't have your desired results the wrong way around? And isn't the solution just a a couple of update statements? You shouldn't really be using the category code as your PK, you should have an independent PK so you can modify the category name without needing to update any products. – Dale K Apr 08 '21 at 21:50

1 Answers1

0

You don't need an SP to accomplish this.

Try temporarily disabling the FK constraint via ALTER TABLE Products NOCHECK CONSTRAINTS ALL

Marcus Vinicius Pompeu
  • 1,181
  • 1
  • 11
  • 24