CODEWITHSUNDEEP
javasql-server
George Kontonikolaou
George Kontonikolaou

Reputation: 109

How i make a Addition from 2 tables and insert the value to one of them

I cant find the right query that make an addition from 2 tables 2 columns inserts and save the data to one of them

Here is my makeorderaction 

 private void Make_OrderActionPerformed(java.awt.event.ActionEvent evt) {                                           
     String query="INSERT INTO Orders(Pro_Id ,Pro_Name,Order_Quantity,Order_Date)VALUES ('"+Pro_Id.getText()+" ','"+Pro_Name.getText()+" ','"+Order_Quantity.getText()+" ','"+Order_Date.getText()+" ') ";
    executeSQLQuery(query,"Inserted");
    String qquery ="UPDATE Products SET Pro_Quantity where Prod_Id = Pro_Quantity+Order_Quantity ";
    // executeSQLQuery(query,"quantity updated");
    }

I need to find a sqlquery that make addition from table Order the column Order_Quantity with the table products the column Pro_Quantity. And make an update in table products the new value in column Pro_Quantity depending each time the identifier of the product any idea ? Ty

Table Orders

CREATE TABLE [dbo].[Orders](
    [Order_Id] [int] IDENTITY(1,1) NOT NULL,
    [Order_Date] [int] NULL,
    [Order_Quantity] [int] NULL,
    [Pro_Id] [int] NULL,
    [Pro_Name] [varchar](50) NULL,
 CONSTRAINT [PK__Orders__F1E4607B714E3A74] PRIMARY KEY CLUSTERED 
(
    [Order_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Products] FOREIGN KEY([Pro_Id])
REFERENCES [dbo].[Products] ([Pro_Id])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Products]
GO

Table Products 

CREATE TABLE [dbo].[Products](
    [Pro_Id] [int] NOT NULL,
    [Pro_Name] [varchar](50) NOT NULL,
    [Pro_Price] [float] NULL,
    [Pro_Quantity] [int] NULL,
    [Pro_Supplier_id] [int] NOT NULL,
    [Pro_Tax] [float] NOT NULL,
    [Cat_products] [varchar](50) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [Pro_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I add this qquery but i think he need some changes 

  String qquery=  " UPDATE Products SET Pro_Quantity= Pro_Quantity + '"+Order_Quantity.getText()+"' FROM Products  INNER JOIN Orders ON   Products.Pro_Id="+Orders.getText() ;

Upvotes: 0

Views: 80

Answers (2)

George Kontonikolaou
George Kontonikolaou

Reputation: 109

i change the above code a bit so he update the last order only. 

String query= "  UPDATE Products SET Pro_Quantity= Products.Pro_Quantity + Orders.Order_Quantity FROM Products INNER JOIN Orders ON Products.Pro_Id= Orders.Pro_Id where Order_Id = (SELECT MAX(Order_Id) FROM Orders)" ; 
        executeSQLQuery(query,"updated");

Upvotes: 1

Mr_Thorynque
Mr_Thorynque

Reputation: 2002

some think like this How do I UPDATE from a SELECT in SQL Server?

    UPDATE P
SET
    P.Pro_Quantity= P.Pro_Quantity + O.Order_Quantity       
FROM
    Products as P
    INNER JOIN Orders as O
        ON P.Pro_Id= O.Pro_Id

Upvotes: 1

Related Questions