Mysql updating multiple tables


25-Jan-2020 21:56

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.



Chat Random - this is a random video chat, access to which is absolutely free.… continue reading »


Read more

The pain and suffering you might have experienced in the past doesn’t have to change you or make you feel cold towards others.… continue reading »


Read more

Tables in the front-end are linked to the back-end.… continue reading »


Read more

Come and Meet thousands of Singles from the UAE , Dubai, Abu Dhabi, Sharjah, Umm al qaywayn,ajman, Al Ayn, Al Fujayrah, Ra's al khaymah. … continue reading »


Read more

They can be expensive compared to others but the service costs a lot since it includes fees to ensure that members are safe.… continue reading »


Read more

Multiple Log File Destinations including syslog (Unix), Event Log (Windows), and a standard log file.… continue reading »


Read more

To get all these versions (no more than about 30MB total) takes no longer than a few minutes, on an average broadband connection. It lives in a case with 8 drive bays, and runs several multibooting systems - from Windows XP to 10, and some Linux distributions without problems, and generally with good speed.… continue reading »


Read more

With similarity in most popular available messengers, yesichat's calling features takes your chat room experience to a whole new level.… continue reading »


Read more