Update column from another table - mySQL 3.5.2

Question

Hi,

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

Answer 1

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

Answer 2

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.

Answer 3

Hi

Suppose i want to select the "products_ordered" field from the INFORMATION_SCHEMA.COLUMNS then how we will write the Query within MySQL.

I am trying this :

update rollno set SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='lastname' limit 0,1='RAM' where id=2;

But it's not working. Any help would be appreciated. Thanks in advance.