Mysql updating multiple tables
Any data that violates any unique index will cause the same problem. For example, I might export some data to a spreadsheet, send it to a client, and the client might update or add some data and return the spreadsheet to me.
That’s a terrible way to update data, but for various reasons, I’m sure many readers have found themselves in a similar situation.
Three queries SELECT 1 FROM `table` WHERE `id` = 20439 for each of the three tables which should be fast and gives me the chance to catch any missing ids at this point.
Then construct an UPDATE query live time as a php string using only the tables where the id was found.
Also, this scenario’s master table has only 5 records; my real master table has close to 1,000,000 records.
Thus, we need an update statement that is efficient.
If performance is the goal and the queries don’t need to be portable, I see no reason not to use the solution that performs best.
My first attempt, which was incorrect, was this: The problem with this update statement was that I realized it would update all 5 records instead of just the 2 records accounting wanted to update; this would result in 103 and 104 being updated to the updated amounts, but all other amounts wiped out to null value.
-- Best practice update ( select m.invoice_amount, a.updated_invoice_amount from master_table m, data_from_accounting a where m.job_number=a.job_number ) set m.invoice_amount=a.updated_invoice_amount; Note that the job number field in both tables in this example scenario are both primary keys.
In order for the “best practice” update statement to work, they must be either primary keys or unique indexes, otherwise the query will fail with the error message “ORA-01779: Cannot modify a column which maps to a non key-preserved table”.
I wanted to UPDATE the tables WHEREVER the ID appeared.
This idea doesn't look possible without using IF EXISTS and sub queries checking for the ID in each table. So if the ID in one table does not exist the whole query will fail.As in life, the most important thing is to find a good balance and determine which criteria really matter.