?
Solved

Update statements between 2 tables

Posted on 2004-03-24
14
Medium Priority
?
226 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

771 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