Databases
StackOverflow2013
Postsdbo
POMultiple Table Update - Same Column For Each Table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple Table Update - Same Column For Each Table
    primarykey
    data
    text
    <p>I have a dynamic amount of tables with the same column structure. I wish to update certain rows in all of them with a single query. Any multi-table UPDATE examples I've found have to do with doing different columns in different tables, and updating one table based on the value in another table. My issue is that I have the same column in all tables, and it is a dynamic number of tables.</p> <p>As an example of this, I would have table_a, table_b, and table_c. All three tables would share the same structure: id and status.</p> <p>I want to update the status column in all three at once to a known value.</p> <p>This is the queries that I would like to essentially combine in this example.</p> <pre><code>UPDATE table_a SET status = 'closed' WHERE id = 5 UPDATE table_b SET status = 'closed' WHERE id = 5 UPDATE table_c SET status = 'closed' WHERE id = 5 </code></pre> <p>Since the amount of tables is dynamic, I feel this would be cleanest with a single query. I have an array of the table names in PHP (loaded dynamically for this system) and can iterate them to form joins or concatenate a string if needed.</p> <p>Here is an example of what I <em>thought</em> would work.</p> <pre><code>UPDATE table_a, table_b, table_c SET status = 'closed' WHERE id = 5 </code></pre> <p>The problem with this query is that I get an error about ambiguity for the column names.</p> <p>Do I need to make it like something this?</p> <pre><code>UPDATE table_a, table_b, table_c SET table_a.status = 'closed', table_b.status = 'closed', table_c.status = 'closed WHERE table_a.id = 5 OR table_b.id = 5 OR table_c.id = 5 </code></pre> <p>I'm assuming this would work and might not be too bad, as the expected amount of tables really ranges from 1-10 maybe. However, I was really hoping for a more efficient way. Firing off individual queries might not be so bad with that few of queries, but in a big system every bit of optimization counts!</p> <p>Thank you for any help.</p> <p>Please note that the structure of having multiple tables with the same scheme is by design, as it is a tracker system that separates multiple locations. The example only reflects the basic structure of the idea I need to implement; the real thing is much more complicated. Also, stored procedures will not be an option in this project.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USDemonslay335
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COMaybe your database design is not to good if you need to do this.
      singulars
      1. POMultiple Table Update - Same Column For Each Table
      1. USTrue Soft
    2. COThis is not a commonly ran query, and is only for a specific feature. The database is designed to keep tickets separate between locations. In order to have each location have it's own separate ticket numbers and everything, separate tables must be made, or separate databases. I opted for tables, as the database itself separates between installations of this software for different clients. Thank you for the input though, I know that going back to the roots to re-evaluate the database structure can be necessary in some cases.
      singulars
      1. POMultiple Table Update - Same Column For Each Table
      1. USDemonslay335
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload