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

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

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

321580 次浏览
小开

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

小开

当您说多个查询时，您是指多个 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 ()调用执行所有三个查询。

小开

您也可以使用一个查询来完成这项工作，如下所示:

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“ join”)。

小开

以两个表为例，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;
小开 
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 语句，例如:

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;

编辑:

为了好玩，我们再加点有趣的东西。

假设您有一个 books表和一个 authors表。你的 books是 author_id。但是当最初创建数据库时，没有设置外键约束，后来前端代码中的一个 bug 导致一些图书添加了无效的 author_id。作为一个数据库管理员，你不需要通过所有这些 books来检查 author_id应该是什么，所以决定由数据捕获器来修复 books，使其指向正确的 authors。但是，有太多的书，通过每一个，让我们说，你知道那些有一个 author_id对应一个实际的 authors0是正确的。只有那些不存在的 author_id是无效的。已经有一个用户界面来更新图书的详细信息，开发人员不希望仅仅为了这个问题而更改界面。但是现有的接口是 authors2，所以所有无效作者的书籍都被排除在外。

你可以这样做: 插入一个假的作者记录，如“未知作者”。然后更新所有不良记录的 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;

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

小开

假设我有一个主键 _id的 Table1和一个布尔值列 doc_availability; 一个外键 _id的 Table2和一个日期时间列 last_update，我想将 Table1中 _id14的文档的可用性改为0，即不可用，并使用文档上次更新时的时间戳更新 Table2。下面的查询将完成这项任务:

UPDATE Table1, Table2
SET doc_availability = 0, last_update = NOW()
WHERE Table1._id = Table2._id AND Table1._id = 14
小开 
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

'UPDATE products,category,subcategoey set 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],

对我有用。


提交答案