Update two tables at one time

ryland22

I have one MySql dbase with two tables: inventory_for_store_1, and inventory_for_store_2. Within these tables are descriptions of products, meta-tags for products, and quantity of products.

The only thing in common between the two tables is the quantity column. The quantity will be the same in both tables.

Instead of having to use 2 seperate SQL update statements to update the quantity everytime a product is sold, is there a more efficient or easier way to maintain the quantity columns in these two tables?

Thanks.

mourisj

I guess if the only common thing in common is the quantity, you have a serious design problem in your database:

if it has to be always updated on both sides, you only need it once. You have a redundancy of data, and databases are meant to avoid these!
if it is the only thing in common, how can you have as a relationship between both tables to know which items to update ?

If you have to update both tables simultanously, there must be other common points than quantity; If you have the same items in both tables, drop one table and put everything in one table.

I don't understand your design, can I have more Information about it?

JJ Mouris

tomhath

An UPDATE statement only works on one table at a time, so you'll need two.

ryland22

Yes, I know I need two update statements, that is what I'm trying to avoid and wondered if there was anyway to update two tables with the same column without having to execute two update statements.

The reason the dbase is structured this way is because the same products are being sold on two different websites. Table 1 has meta-tags stored in it for store one, while table 2 has different meta-tags stored in it for store two.

The quantity column is the only one that needs to be updated in both tables.

tomhath

The two tables don't have the same column. You have two tables, each has it's own columns. The fact that you happen to have the same column name in both tables means nothing to the dbms.

If you want to update both tables, you'll have to update both tables. With two update statements.

ryland22

Yes, that is correct, there are two tables each with it's own column called "quantity" and both of those columns should have exactly the same values- that is why I referred to them as the same.

Just wondered if there was some way of updating two columns in two tables with the same name and values, without doing two update statements.