mysql - 如何查看更新查询已更改的所有值？在mysql中

Question

大家好，我的 sql 遇到了新问题...

我尝试更新我的商店中分配给特定类别的所有文章。

我为其构建了一个查询:

update s_articles_prices
INNER JOIN s_articles on s_articles_prices.articleID=s_articles.id
INNER JOIN s_articles_supplier on s_articles.supplierID=s_articles_supplier.id
INNER JOIN s_articles_details on s_articles.id=s_articles_details.articleID
SET price = price + 10
WHERE s_articles_supplier.name="category name"

现在我想知道我的查询更新了哪些值...

我也有一个选择查询，我可以连接这两个查询以准确显示我已使用更新查询更新的表吗？

这是我的选择:

SELECT s_articles.id as aid, s_articles.name as aName, s_articles.supplierID as sID, s_articles_prices.pricegroup as pricegroup, s_articles_prices.price as price, s_articles_supplier.name as sName, s_articles_details.ordernumber as sku
FROM s_articles
INNER JOIN s_articles_prices on s_articles.id=s_articles_prices.articleID
INNER JOIN s_articles_supplier on s_articles.supplierID=s_articles_supplier.id
INNER JOIN s_articles_details on s_articles.id=s_articles_details.articleID
WHERE s_articles_supplier.name="category name"

我希望有一个与现在选择类似的输出，这样我就可以一目了然地检查我的更新是否有效

感谢您的宝贵时间:)

Answer 1

在 DiarSelimi 和 RaymondNijland 的帮助下，我解决了我的问题。

我现在这样做了:

我已经用这部分创建了一个sql脚本:

创建临时表:

CREATE TEMPORARY TABLE snapTable 
SELECT s_articles.id as aid, s_articles.name as aName, s_articles.supplierID as sID, s_articles_prices.pricegroup as pricegroup, s_articles_prices.price as price, s_articles_supplier.name as sName, s_articles_details.ordernumber as sku
FROM s_articles
INNER JOIN s_articles_prices on s_articles.id=s_articles_prices.articleID
INNER JOIN s_articles_supplier on s_articles.supplierID=s_articles_supplier.id
INNER JOIN s_articles_details on s_articles.id=s_articles_details.articleID
WHERE s_articles_supplier.name="category name";

运行我的更新:

update s_articles_prices 
INNER JOIN s_articles on s_articles_prices.articleID=s_articles.id 
INNER JOIN s_articles_supplier on s_articles.supplierID=s_articles_supplier.id 
INNER JOIN s_articles_details on s_articles.id=s_articles_details.articleID 
SET price = price + 10 
WHERE s_articles_supplier.name="category name";

并将我的临时表与实时表进行比较:

SELECT aid, sku, snapTable.price, s_articles_prices.price as newPrice 
FROM snapTable
INNER JOIN s_articles on snapTable.aid=s_articles.id
INNER JOIN s_articles_prices on snapTable.aid=s_articles_prices.articleID;

你会得到类似的东西:

aid|sku     |price            |newPrice
-------------------------------------------------
2  |number  |202.60504201681  |212.60504201681
2  |number  |202.60504201681  |212.26050420167996

也许对其他人也有帮助:)