更新另一个表中的列 - mySQL 3.5.2

发布于 2024-07-06 11:31:42 字数 635 浏览 3 评论 0原文

我尝试了几种方法来从另一个表更新 mySQL 数据库表中的列，但没有任何运气。

我在某处读到版本 3.5.2 不支持多表更新，我需要一个基于代码的解决方案 - 这是正确的吗？

如果没有，有人可以使用 sql 为我指明正确的方向吗？

UPDATE products SET products_ordered = (
    SELECT SUM(products_quantity) 
    FROM orders_products 
    WHERE products_id = products.products_id
);

或者：

Create temporary table my_temp_table
as
SELECT products_id, SUM(products_quantity) as total 
FROM orders_products 
GROUP BY products_id 

UPDATE products, my_temp_table
SET products.products_ordered = my_temp_table.total 
WHERE products.products_id = my_temp_table.products_id

原文

I've tried a couple of approaches to update a column in a mySQL database table from another table but am not having any luck.

I read somewhere that version 3.5.2 does not support multi-table updates and I need a code-based solution - is that correct?

If not can anybody point me in the right direction using sql?

UPDATE products SET products_ordered = (
    SELECT SUM(products_quantity) 
    FROM orders_products 
    WHERE products_id = products.products_id
);

or:

Create temporary table my_temp_table
as
SELECT products_id, SUM(products_quantity) as total 
FROM orders_products 
GROUP BY products_id 

UPDATE products, my_temp_table
SET products.products_ordered = my_temp_table.total 
WHERE products.products_id = my_temp_table.products_id

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

老旧海报 2024-07-13 11:31:42

当我使用不支持子查询或多表更新的MySQL时，我使用了一个技巧来完成你所描述的操作。运行一个查询，其结果本身就是 SQL 语句，然后保存输出并将其作为 SQL 脚本运行。

SELECT CONCAT( 
  'UPDATE products SET products_ordered = ', 
   SUM(products_quantity), 
  ' WHERE product_id = ', products_id, ';') AS sql_statement
FROM orders_products
GROUP BY products_id;

顺便说一下，据我所知，没有MySQL 3.5.x这样的版本。我认为您可能报告了错误。或者您正在使用其他产品，例如 mSQL。

编辑：我忘记在上面的查询生成的 SQL 语句中添加分号。

When I used to use MySQL that did not support either subqueries or multi-table updates, I used a trick to do what you're describing. Run a query whose results are themselves SQL statements, and then save the output and run that as an SQL script.

SELECT CONCAT( 
  'UPDATE products SET products_ordered = ', 
   SUM(products_quantity), 
  ' WHERE product_id = ', products_id, ';') AS sql_statement
FROM orders_products
GROUP BY products_id;

By the way, there is no such version MySQL 3.5.x as far as I know. I think you may have reported that wrong. Or else you're using another product such as mSQL.

Edit: I forgot to add a semicolon into the SQL statement generated by the query above.

回复收藏 0 原文