Solved

Update statements between 2 tables

Posted on 2004-03-24
14
221 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
Comment Utility
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
Comment Utility
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
Comment Utility
There shouldn't be a need for a subselect in this case. But I did forget to remove the close parenthesis...
0
 

Author Comment

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

Expert Comment

by:eicheled
Comment Utility
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
Comment Utility
yeah, you need to remove the close parenthesis at the last line
0
 

Author Comment

by:annie613
Comment Utility
ok so when i remove it
java will not built the file
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:annie613
Comment Utility
//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
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
My pleasure! :-)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
What is the compatible hibernate version for spring 4.3.1 10 59
array6 challenfge 6 62
network + 7 72
solarwind tftp server 2 29
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
The viewer will learn how to implement Singleton Design Pattern in Java.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now