Updating Multiple Observations at the Same Time with a SQL Stored Procedure

Question

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
...          ...

Answer 1

You don't need an SP to accomplish this.

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

• https://stackoverflow.com/a/159064/206413
• https://learn.microsoft.com/en-us/sql/relational-databases/tables/disable-check-constraints-with-insert-and-update-statements?view=sql-server-ver15