从另一个表更新列 - mySQL 3.5.2

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.

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

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

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

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

如果没有,任何人都可以使用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

2 个解决方案

#1

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.

当我以前使用不支持子查询或多表更新的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;

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.

顺便说一下,据我所知,没有MySQL 3.5.x这样的版本。我想你可能报错了。否则你正在使用其他产品,如mSQL。

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

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

#2

Multi-table updates are not support in MySQL <= 4.0.4 I would highly recommend to update your server to MySQL 5.0.xx

MySQL <= 4.0.4不支持多表更新我强烈建议您将服务器更新到MySQL 5.0.xx

#1

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;