I keep having trouble with a product quantity schema I have designed.
It's signs the design is flawed perhaps, but at this point I'm trying to make things work well and not just simply work.
I have a product table with product id and product info....
CREATE TABLE products (
product_id int(11) NOT NULL AUTO_INCREMENT,
product_title varchar(200) NOT NULL,
product_image varchar(1000) NOT NULL,
product_description varchar(10000) NOT NULL,
product_value int(11) NOT NULL,
product_points int(11) NOT NULL,
product_date datetime NOT NULL,
product_status int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (product_id)
)
I then have a table with product quantity history to keep track of when a product's quantity was updated and the numbers involved.
CREATE TABLE product_quantity (
quantity_id int(11) NOT NULL AUTO_INCREMENT,
product_id int(11) NOT NULL,
quantity_date datetime NOT NULL,
quantity_original int(11) NOT NULL,
quantity_remaining int(11) NOT NULL,
PRIMARY KEY (quantity_id)
)
its worked fine and its' working fine till today with help from NogDog helping me retrieve the most current update to a product's quanity and using those figures along side other queries to keep my code tidy.
The problem I have now is this:
CREATE TABLE orders (
cart_id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
product_id int(11) NOT NULL,
quantity int(11) NOT NULL,
status int(11) NOT NULL,
date datetime NOT NULL,
PRIMARY KEY (cart_id)
)
Basically, I have a very simple task of taking the quanities from orders and subtracting them on the product_quantity table.
Which is simple enough for me to do with a loop, however I am trying to optimize the code, and since I have in almost every other instance updated or inserted my tables with info from other tables I would like to continue doing so.
Here is my current queries as it stands. It involves other tables because the order table is actually broken up into several
$SQL="
UPDATE `product_quantity` AS `a`
INNER JOIN orders AS o ON `o`.`order_id` = '2'
INNER JOIN orders_items AS i ON i.order_id = o.order_id
INNER JOIN carts AS p ON p.cart_id = i.cart_id
INNER JOIN product_quantity AS q ON p.product_id = q.product_id
SET `a`.`quantity_remaining` = `q`.`quantity_remaining` - `p`.`quantity`
WHERE q.quantity_id = (
SELECT quantity_id FROM product_quantity
WHERE product_id = p.product_id AND quantity_remaining > 0
ORDER BY quantity_id DESC
LIMIT 1
)
"
but I recieve this error:
#1093 - You can't specify target table 'a' for update in FROM clause
I did some reasearch and saw I could put my subquery in another subquery but that did not resolve my problem as now references to other tables did not work
