Solved

Update statements between 2 tables

Posted on 2004-03-24
14
222 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
starter POM and spring-boot-starter,  spring-boot-web 2 57
web application structure 18 77
servlet example 17 32
spring jars download 1 28
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
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…
This video teaches viewers about errors in exception handling.

803 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