I have attempted a few methods to update a column inside a mySQL database table from another table but am not getting any luck.
I just read somewhere that version 3.5.2 doesn't support multi-table updates and that i require a code-based solution - is the fact that correct?
Otherwise can anybody point me within the right direction using 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
When I did previously use MySQL that didn't support either subqueries or multi-table updates, I made use of a trick to complete what you are explaining. Operate a query whose answers are themselves SQL claims, after which save the output and run that being 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;
Incidentally, there's no such version MySQL 3.5.x so far as I understand. I believe you might have reported that wrong. Otherwise you are using another product for example mSQL.
Edit: I didn't remember to include a semicolon in to the SQL statement produced through the query above.
Multi-table updates aren't support in MySQL <= 4..4 I'd recommend to improve your server to MySQL 5..xx