asked on

Update statements between 2 tables

i was advised "You very probably want to have some Java experts look at this question..." regards jack

i have a function in my java code called

subtractResources(){} this allows when a customer from one panel orders a product in a cart

what i want my update statement to do is subtract from the Product Table

my tables are cart and product
and i have a field in cart called Qty
so when the customer chooses the Qty
i want this to happen

Product.ProductInventroy = Product.ProductInventory - Cart.Qty WHERE Product.ProductID = Cart.ProductID

but im having a hard time setting up this UPDATE

this is what i have my code as, can anyone help??? thanks in advance annie :)

public void subtractResources()
{
//show graphically on 3 meters the product level going down
//this will happen later not worrying about it now

try
{

Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();

System.out.println("subtractResources was invoked!");//just to test

//update in the DB DeliProduct.ProductInvetory - DeliCart.Qty
stmt.executeUpdate("UPDATE DeliProduct, DeliCart SET DeliProduct.ProductInventory = DeliProduct.ProductInventory - DeliCart.Qty WHERE DeliProduct.ProductID = DeliCart.ProductID");

stmt.close();
con.close();
}
catch (Exception e)
{
System.out.println(e);
}

}//end subtractResources

Tommy Braas

Change the statement to:

UPDATE DeliProduct
SET DeliProduct.ProductInventory = DeliProduct.ProductInventory - DeliCart.Qty
FROM DeliProduct, DeliCart
WHERE DeliProduct.ProductID = DeliCart.ProductID)

SOLUTION

eicheled

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tommy Braas

There shouldn't be a need for a subselect in this case. But I did forget to remove the close parenthesis...

annie613

ASKER

orange
im recieving an error when i run ur code
Syntax error )missing operator in query expression

eicheled

In his last post he mentioned that he forgot to remove the ')', so take that out and try it again.

Tommy Braas

yeah, you need to remove the close parenthesis at the last line

annie613

ASKER

ok so when i remove it
java will not built the file

annie613

ASKER

//this builts but i receive that error

try
{
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();

System.out.println("subtractResources was invoked!");

//update in the DB DeliProduct.ProductInvetory - DeliCart.Qty
stmt.executeUpdate("UPDATE DeliProduct SET DeliProduct.ProductInventory = DeliProduct.ProductInventory - DeliCart.Qty FROM DeliProduct, DeliCart WHERE DeliProduct.ProductID = DeliCart.ProductID");

stmt.close();
con.close();
}
catch (Exception e)
{
System.out.println(e);
}

annie613

ASKER

ok how about this then

instead of subtracting the two fields

i would like to make whatever Cart.Qty is and set that equal to Product.ProductInventory

can that be done with an UPDATE STATEMENT

try
{
Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();

System.out.println("subtractResources was invoked!");

//update in the DB
stmt.executeUpdate("UPDATE DeliProduct SET DeliProduct.ProductReorder= DeliCart.Qty FROM DeliProduct, DeliCart WHERE DeliProduct.ProductID = DeliCart.ProductID");

stmt.close();
con.close();
}
catch (Exception e)
{
System.out.println(e);
}

annie613

ASKER

this works :)

stmt.executeUpdate("UPDATE DeliProduct, DeliCart SET DeliProduct.ProductReorder = DeliCart.Qty WHERE DeliProduct.ProductID = DeliCart.ProductID");

stmt.close();
con.close();

ASKER CERTIFIED SOLUTION

Tommy Braas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

annie613

ASKER

and so does this

stmt.executeUpdate("UPDATE DeliProduct, DeliCart SET DeliProduct.ProductInventory = DeliProduct.ProductInventory - DeliCart.Qty WHERE DeliProduct.ProductID = DeliCart.ProductID");

annie613

ASKER

i just wanted to thank orange and eicheled
you both helped me think out loud
and i found the solution
but you both set me in the correct direction
cheers :)

Tommy Braas

My pleasure! :-)