Solved

Update statements between 2 tables

Posted on 2004-03-24
14
224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 25 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 25 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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

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…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

733 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