?
Solved

Update statements between 2 tables

Posted on 2004-03-24
14
Medium Priority
?
229 Views
Last Modified: 2010-03-31
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
   
0
Comment
Question by:annie613
  • 7
  • 5
  • 2
14 Comments
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10670658
Change the statement to:

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

Assisted Solution

by:eicheled
eicheled earned 100 total points
ID: 10670782
orangehead911 - Did you miss the subselect?

UPDATE DeliProduct
SET DeliProduct.ProductInventory = (SELECT DeliProduct.ProductInventory - DeliCart.Qty
FROM DeliProduct, DeliCart
WHERE DeliProduct.ProductID = DeliCart.ProductID)
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10671147
There shouldn't be a need for a subselect in this case. But I did forget to remove the close parenthesis...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:annie613
ID: 10671321
orange
im recieving an error when i run ur code
Syntax error )missing operator in query expression
0
 
LVL 3

Expert Comment

by:eicheled
ID: 10671340
In his last post he mentioned that he forgot to remove the ')', so take that out and try it again.
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10671350
yeah, you need to remove the close parenthesis at the last line
0
 

Author Comment

by:annie613
ID: 10671395
ok so when i remove it
java will not built the file
0
 

Author Comment

by:annie613
ID: 10671415
//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);
        }
0
 

Author Comment

by:annie613
ID: 10671452
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);
        }
0
 

Author Comment

by:annie613
ID: 10671540
this works :)

  stmt.executeUpdate("UPDATE DeliProduct, DeliCart SET DeliProduct.ProductReorder = DeliCart.Qty WHERE DeliProduct.ProductID = DeliCart.ProductID");
           
            stmt.close();
            con.close();
0
 
LVL 14

Accepted Solution

by:
Tommy Braas earned 100 total points
ID: 10671544
>> can that be done with an UPDATE STATEMENT
Yes, that can be done.

However, you might want to update the reorder count with the old value plus what is in the cart, just like the previous problem you had.
0
 

Author Comment

by:annie613
ID: 10671554
and so does this

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

Author Comment

by:annie613
ID: 10672560
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 :)
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 10672585
My pleasure! :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month13 days, 12 hours left to enroll

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question