Update 2 tables in single query

Alter PROCEDURE [dbo].[usp_UpdateProducts]
 (@ProductID int,
  @DisplayName varchar,
  @purchasefee varchar,
  @RepaymentPeriod varchar,
  @BalanceComputingMethod varchar,
  @ModifiedBy varchar,
  @ModifiedOn datetime)
      
AS
BEGIN
      
   SET NOCOUNT ON;
   BEGIN TRANSACTION
   UPDATE [DB1].[dbo].[t_ProductDetails]
    SET T1.[ProductID] = @ProductID
      ,T1.[Purchase] =  @purchasefee
      ,T1.[Repayment_Period] = @RepaymentPeriod
      ,T1.[Balance_computing_Method] = @BalanceComputingMethod
      ,T1.[ModifiedBy] =  @ModifiedBy
      ,T1.[ModifiedOn] = @ModifiedOn
    FROM [dbo].[t_ProductDetails] T1,[dbo].[t_Product] T2
    WHERE T1.[ProductID] =  T2.[ProductID]
    and T1.[ProductID] = @ProductID


    UPDATE [DB1].[dbo].[t_Product]
    SET T2.[ProductID] = @ProductID
      ,T2.[ProductDisplayName] = @DisplayName
    FROM [dbo].[t_ProductDetails] T1,[dbo].[t_Product] T2
    WHERE T1.[ProductID] =  T2.[ProductID]
    and T1.ProductID = @ProductID
 

COMMIT
END

GO

I am not able to create the above procedure.The multi-part identifier "T1.ProductID" could not be bound.

How to make it work.

Thanks
KavyaVSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher GordonSenior Developer AnalystCommented:
You can drop the table alias in front of the fields you are trying to update in your T-SQL statement.  It's always going to update the table you've specified immediately after your UPDATE statement.  This may be what is causing the issue.
0
LowfatspreadCommented:
please explain what you are attempting to do in the procedure...

which database are you executing the alter statement in?

currently i see no reason why these

FROM [dbo].[t_ProductDetails] T1,[dbo].[t_Product] T2
    WHERE T1.[ProductID] =  T2.[ProductID]
    and T1.ProductID = @ProductID


shouldn't just be

where productid=@productid

but i'm not convinced that will fix your current error...

is db1 the real name of your database?
is that supposed to be the "current" database for the procedure?
0
KavyaVSAuthor Commented:
db1s not real name.
Now I tried the following.
It is giving the error at Update statement.  UPDATE [dbo].[t_ProductDetails],[dbo].[t_Product]  
Incorrect syntax near ','.
How to fix it.

Alter PROCEDURE [dbo].[usp_UpdateProducts]
 (@ProductID int,
  @DisplayName varchar,
  @purchasefee varchar,
  @RepaymentPeriod varchar,
  @BalanceComputingMethod varchar,
  @ModifiedBy varchar,
  @ModifiedOn datetime)
     
AS
BEGIN
     
   SET NOCOUNT ON;
   BEGIN TRANSACTION
   UPDATE [dbo].[t_ProductDetails],[dbo].[t_Product]  
    SET [dbo].[t_ProductDetails].[ProductID] = @ProductID
       ,[dbo].[t_Product].[DisplayName]= @DisplayName  
      ,[dbo].[t_ProductDetails].[Purchase] =  @purchasefee
      ,[dbo].[t_ProductDetails].[Repayment_Period] = @RepaymentPeriod
      ,[dbo].[t_ProductDetails].[Balance_computing_Method] = @BalanceComputingMethod
      ,[dbo].[t_ProductDetails].[ModifiedBy] =  @ModifiedBy
      ,[dbo].[t_ProductDetails].[ModifiedOn] = @ModifiedOn
    FROM [dbo].[t_ProductDetails] T1,[dbo].[t_Product] T2
    WHERE T1.[ProductID] =  T2.[ProductID]
    and T1.[ProductID] = @ProductID


     

COMMIT
END

GO

Thanks
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Christopher GordonSenior Developer AnalystCommented:
You can't specify two tables in the update clause like that.  Write a separate update statement for each table.
0
Christopher GordonSenior Developer AnalystCommented:
I'm not sure what all of the joins are for.  I don't understand your business logic, but would something like the attached code do the trick?

Note:  You also need to specify the length of your varchar input parameters to the stored proc.

I'm also assuming your running this in a test environment.
Alter PROCEDURE [dbo].[usp_UpdateProducts]
 (@ProductID int,
  @DisplayName varchar(50),
  @purchasefee varchar(50),
  @RepaymentPeriod varchar(50),
  @BalanceComputingMethod varchar(50),
  @ModifiedBy varchar(50),
  @ModifiedOn datetime)
      
AS
BEGIN
      
   SET NOCOUNT ON;
   BEGIN TRANSACTION
   
   
   UPDATE [t_ProductDetails]
    SET 
		
        [Purchase] =  @purchasefee
      , [Repayment_Period] = @RepaymentPeriod
      , [Balance_computing_Method] = @BalanceComputingMethod
      , [ModifiedBy] =  @ModifiedBy
      , [ModifiedOn] = @ModifiedOn
   
    FROM [t_ProductDetails]
    WHERE [ProductID] = @ProductID
   
    UPDATE [t_Product]
    SET 
		
        [ProductDisplayName] = @DisplayName
      
    FROM [t_Product]
    where [ProductID] = @ProductID
    
COMMIT
END

GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
please answer my questions
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
The bottom line in an update statement is that if you want to update a table, you are allowed 1 table to update in each statement.  Updating 2 tables in a single update statement is not possible.

So the other issue, is that if you specify a table in the Update statement and you do not use it in the FROM clause, then it is an invalid update statement.

Typically you will update the table and can do it like this:

UPDATE T1
SET FieldName = T2.ProductName
FROM dbo.Table1 T1
INNER JOIN dbo.Table2 T2 ON T1.ProductID = T2.ProductID
WHERE T1.ProductID = @ProductID

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.