CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
user3487244
user3487244

Reputation: 147

Updating column and insert a row

I am trying to accomplish to update the column ListPrice adding 25 to its current value where the name is All-Purpose Bike Stands and update SellStartDate to tomorrow's date in the table Production.Product.

Using that updated price I want to insert a row into the table Production.ProductListHistory with the new ListPrice and also update the row in the Production.ProductHistory column named EndDate with tomorrow's date as well.

The tables definitions areas follows:

Production.Product:

ProductID int PK
Name varchar(50)
ProductNumber nvarchar(25)  
ListPrice  money
SellStartDate datetime
SellEndDate

Production.ProductListHistory:

ProductID int PK FK
StartDate datetime PK
EndDate datetime
ListPrice money

Here is what I have so far that continues to give me errors:

CREATE PROCEDURE UPDATE_AND_INSERT
    (@newprice money)
AS
BEGIN
    UPDATE AdventureWorks2008R2.Production.Product 
    SET @newprice = 25 + AdventureWorks2008R2.Production.Product.ListPrice
    WHERE AdventureWorks2008R2.Production.Product.Name LIKE 'All-Purpose%'

    @newprice = AdventureProduct.Production.Listprice
END

Upvotes: 1

Views: 81

Answers (1)

sqluser
sqluser

Reputation: 5672

If I understood your question, Your UPDATE should be something like this

You can also create a table variable to store your deleted list price and then insert it into your history table 

DECLARE @MyTableVar table(
ProductID int NOT NULL,
StartDate datetime,
EndDate datetime,
ListPrice money);

UPDATE AdventureWorks2008R2.Production.Product 
Set ListPrice = 25 + AdventureWorks2008R2.Production.Product.ListPrice
OUTPUT inserted.ProductID,
       inserted.SellStartDate,
       inserted.SellEndDate,
       deleted.ListPrice
INTO @MyTableVar 
where AdventureWorks2008R2.Production.Product.Name Like 'All-Purpose%'
AND Listprice = AdventureProduct.Production.Listprice

Upvotes: 1

Related Questions