多表更新 - 每个表的同一列 [英] Multiple Table Update - Same Column For Each Table

问题描述

我有大量具有相同列结构的表.我希望使用单个查询更新所有行中的某些行.我发现的任何多表 UPDATE 示例都与在不同表中处理不同列有关，并根据另一个表中的值更新一个表.我的问题是我在所有表中都有相同的列，而且它是一个动态数量的表.

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.

作为一个例子，我有 table_a、table_b 和 table_c.所有三个表将共享相同的结构:id 和 status.

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.

我想一次将所有三个中的状态列更新为已知值.

I want to update the status column in all three at once to a known value.

这是我想在这个例子中基本上结合的查询.

This is the queries that I would like to essentially combine in this example.

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

由于表的数量是动态的，我觉得使用单个查询会最干净.我在 PHP 中有一个表名数组(为此系统动态加载)，并且可以迭代它们以形成连接或在需要时连接一个字符串.

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.

这是我认为可行的示例.

UPDATE table_a, table_b, table_c
SET status = 'closed'
WHERE id = 5

这个查询的问题是我收到一个关于列名不明确的错误.

The problem with this query is that I get an error about ambiguity for the column names.

我需要把它弄成这样吗?

Do I need to make it like something this?

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

我假设这会起作用并且可能不会太糟糕，因为预期的表数量可能真的在 1-10 之间.但是，我真的希望有一种更有效的方法.使用少量查询触发单个查询可能还不错，但在大型系统中，每一点优化都很重要！

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!

感谢您的帮助.

请注意，具有相同方案的多个表的结构是设计使然，因为它是一个分隔多个位置的跟踪器系统.例子只反映了我需要实现的想法的基本结构；实际情况要复杂得多.此外，存储过程将不在此项目中.

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.

推荐答案

是的，您需要限定要更新的每个表/列，如果它们使用相同的逻辑名称.

Yes, you'll need to qualify each table / column that you want to update if they use the same logical names.

查询发现您正在寻找status"的逻辑名称，但您已将status"定义为数据库中 3 个不同物理位置的逻辑名称.你说的是哪一个?当涉及多个表时，可以做很多事情.因此，对于假设您要更新所有数据库的数据库来说，这将是非常糟糕的.

The query sees you're looking for a logical name of 'status', but you've defined 'status' to be the logical name for 3 different physical locations in the database. Which one did you mean? There are many things that can be done when multiple tables are involved. So for a database to assume you want to update all of them would be very bad.

这篇关于多表更新 - 每个表的同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！