如何使用Access ODBC通过一个查询更新多个表? [英] How can I update multiple tables with one query using Access ODBC?

问题描述

我已经进行了彻底的搜索，但找不到该问题的答案.我连接到Access ODBC，需要运行一个查询，该查询根据一个值同时更新许多表.

I've done a thorough search but can't find an answer to this question. I connect with Access ODBC and need to run a query which updates many tables at the same time based on one value.

这是我目前正在做的事情:

Here's what I'm doing at the moment:

$DSN="accessodbc";
$DSN_User="myusername";
$DSN_Passwd="mypassword";
$objConnect = odbc_connect( $DSN, $DSN_User, $DSN_Passwd );

$strSQL1="UPDATE table1 SET column1='dup' WHERE User=" . $user;
$strSQL2="UPDATE table2 SET column1='dup' WHERE User=" . $user;
$strSQL3="UPDATE table3 SET column1='dup' WHERE User=" . $user;

$objExec1 = odbc_exec($objConnect, $strSQL1);
$objExec2 = odbc_exec($objConnect, $strSQL2);
$objExec3 = odbc_exec($objConnect, $strSQL3);

...依此类推，大约有50张桌子.

...and so on for about 50 tables.

这可行，但是显然不是资源上最好的解决方案.有没有一种方法可以通过一个查询更新所有表?

This works but is obviously not the best solution resource-wise. Is there a way to update all the tables with one query?

谢谢.

推荐答案

尽管用单个SQL语句更新多个表是很不常见的，但在这种情况下，您可以做到(尽管可以在约50个表中实现)麻烦多于其价值).

While it is fairly unusual to UPDATE more than one table with a single SQL statement, in this particular case you probably can (although implementing this for ~50 tables might be more trouble than it's worth).

为了记录，我刚刚在Access 2010中测试了以下内容并在测试表中进行了测试

For the record, I just tested the following in Access 2010 and for the test tables

[table1]
User  column1
----  -------
Gord
Bob          

[table2]
User   column1
-----  -------
Gord
Homer         

[table3]
User      column1
--------  -------
Gord
Quagmire         

查询

UPDATE
    (
        table1
        INNER JOIN
        table2
            ON table1.User = table2.User
    )
    INNER JOIN
    table3
        ON table2.User = table3.User
SET
    table1.column1 = 'dup',
    table2.column1 = 'dup',
    table3.column1 = 'dup'
WHERE table1.User = 'Gord'

产生

[table1]
User  column1
----  -------
Gord  dup    
Bob          

[table2]
User   column1
-----  -------
Gord   dup    
Homer         

[table3]
User      column1
--------  -------
Gord      dup    
Quagmire         

这篇关于如何使用Access ODBC通过一个查询更新多个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！