MySQL，用一个查询更新多个表

Question

我有一个更新三个表的函数，但我使用三个查询来执行此操作。我希望使用更方便的方法来进行良好的实践。

如何使用单个查询更新 MySQL 中的多个表？

Answer 1

以两个表为例：Books 和 Orders。如果我们在 Orders 表中使用 Order.ID = 1002 增加特定订单中的图书数量，那么我们还需要减少我们的库存在 Books 表中具有相同的编号。

UPDATE Books, Orders
SET Orders.Quantity = Orders.Quantity + 2,
    Books.InStock = Books.InStock - 2
WHERE
    Books.BookID = Orders.BookID
    AND Orders.OrderID = 1002;

Answer 2

UPDATE t1
INNER JOIN t2 ON t2.t1_id = t1.id
INNER JOIN t3 ON t2.t3_id = t3.id
SET t1.a = 'something',
    t2.b = 42,
    t3.c = t2.c
WHERE t1.a = 'blah';

要查看要更新的内容，您可以将其转换为选择语句，例如：

SELECT t2.t1_id, t2.t3_id, t1.a, t2.b, t2.c AS t2_c, t3.c AS t3_c
FROM t1
INNER JOIN t2 ON t2.t1_id = t1.id
INNER JOIN t3 ON t2.t3_id = t3.id
WHERE t1.a = 'blah';

使用与其他答案相同的表的示例：

SELECT Books.BookID, Orders.OrderID,
    Orders.Quantity AS CurrentQuantity,
    Orders.Quantity + 2 AS NewQuantity,
    Books.InStock AS CurrentStock,
    Books.InStock - 2 AS NewStock
FROM Books
INNER JOIN Orders ON Books.BookID = Orders.BookID
WHERE Orders.OrderID = 1002;

UPDATE Books
INNER JOIN Orders ON Books.BookID = Orders.BookID
SET Orders.Quantity = Orders.Quantity + 2,
    Books.InStock = Books.InStock - 2
WHERE Orders.OrderID = 1002;

编辑：

只是为了好玩，让我们添加一些更有趣的内容。

假设您有一个书籍表和一个作者表。您的图书有一个author_id。但最初创建数据库时，没有设置外键约束，后来前端代码的一个错误导致一些书籍添加了无效的author_id。作为 DBA，您不想通过所有这些书籍来检查 author_id 应该是什么，因此决定数据捕获器将修复books 指向正确的作者。但是书籍太多，无法逐一浏览，假设您知道那些具有与实际 author 相对应的 author_id 的书籍是正确的。只有 author_id 不存在的那些才是无效的。已经有一个接口供用户更新书籍详细信息，并且开发人员不想仅仅因为这个问题而更改该接口。但现有的接口会进行INNER JOIN Authors，因此所有具有无效作者的书籍都被排除在外。

你可以做的是：插入一个假作者记录，如“未知作者”。然后更新所有不良记录的author_id以指向未知作者。然后数据捕获器可以搜索作者设置为“未知作者”的所有书籍，查找正确的作者并修复它们。

如何更新所有不良记录以指向未知作者？像这样（假设未知作者的 author_id 为 99999）：

UPDATE books
LEFT OUTER JOIN authors ON books.author_id = authors.id
SET books.author_id = 99999
WHERE authors.id IS NULL;

以上还将更新具有 NULL author_id 的 books > 致未知作者。如果您不希望这样，当然可以添加AND books.author_id IS NOT NULL。

Answer 3

您也可以使用像这样的联接来通过一个查询来完成此操作：

UPDATE table1,table2 SET table1.col=a,table2.col2=b
WHERE items.id=month.id;

当然，然后只需发送这个查询。您可以在此处阅读有关联接的更多信息： http://dev.mysql.com /doc/refman/5.0/en/join.html。对于多个表更新的排序和限制还有一些限制，您可以在此处阅读：http://dev.mysql.com/doc/refman/5.0/en/update.html（只需 ctrl+f“加入”）。

Answer 4

这通常就是存储过程的用途：按顺序执行多个 SQL 语句。使用回滚，您可以确保将它们视为一个工作单元，即它们要么全部执行，要么都不执行，以保持数据一致。

Answer 5

当您说多个查询时，您的意思是多个 SQL 语句，如：

UPDATE table1 SET a=b WHERE c;
UPDATE table2 SET a=b WHERE d;
UPDATE table3 SET a=b WHERE e;

或多个查询函数调用，如：

mySqlQuery(UPDATE table1 SET a=b WHERE c;)
mySqlQuery(UPDATE table2 SET a=b WHERE d;)
mySqlQuery(UPDATE table3 SET a=b WHERE e;)

前者都可以使用单个 mySqlQuery 调用来完成，如果这是您想要实现的，只需调用下面的 mySqlQuery 函数方式：

mySqlQuery(UPDATE table1 SET a=b WHERE c; UPDATE table2 SET a=b WHERE d; UPDATE table3 SET a=b WHERE e;)

这将通过一次 mySqlQuery() 调用执行所有三个查询。

Answer 6

假设我有 Table1 ，其中包含主键 _id 和布尔列 doc_availability； Table2 具有外键 _id 和 DateTime 列 last_update，我想更改 _id 14 文档的可用性将 Table1 中的值设置为 0，即不可用，并使用文档上次更新时的时间戳更新 Table2。以下查询将完成该任务：

UPDATE Table1, Table2 
SET doc_availability = 0, last_update = NOW() 
WHERE Table1._id = Table2._id AND Table1._id = 14

Answer 7

let id = req.params.id;
let pname = req.body.pname;
let catname = req.body.catname;
let scatname = req.body.scatname;
let price = req.body.price;
let available_qty = req.body.available_qty

'更新产品、类别、子类别设置category.name=?,subcategoey.name=?, products.name=?, products.price=?, products.available_qty=? WHERE Category.id= subcategoey.cid AND subcategoey.id = products.scid AND products.id=?',[catname,scatname,pname,price,available_qty,id],

它对我有用...

Answer 8

问题已经得到解答，但还有一件事需要注意。

JOIN 查询必须位于 SET 查询之前

这将起作用

UPDATE post p
JOIN user u ON p.user_id = u.id
SET p.is_deleted = 1, u.state = "disabled"  
WHERE u.email = '[email protected]'

这将不起作用

UPDATE post p
SET p.is_deleted = 1, u.state = "disabled" 
JOIN user u ON p.user_id = u.id 
WHERE u.email = '[email protected]'