none
Correct way to do update?

    Question

  • Hi 

    I'm trying to accomplish the following update. What is the correct and most efficient way to do this?

    • I want to update #tmp2 with the price in #tmp
    • Where the date in #tmp is equal to the date in #tmp2
    • If there is no date then use the latest date from #tmp.

    the two tables join on #tmp.pkiProdId = #tmp2.fkiProdID

    create table #tmp (pkiProd int,dteDate datetime,numPrice decimal (18,2))
    insert into #tmp select 1,'2013-01-01',1
    union all
    select 1,'2013-01-02',1.5
    union all 
    select 1,'2013-01-03',1.7
    union all 
    select 1,'2013-01-04',1.9
    union all 
    select 1,'2013-01-05',2
    union all 
    select 1,'2013-01-06',2.4
    union all 
    select 1,'2013-01-07',3.4
    
    
    create table #tmp2 (pkiTransID int identity, fkiProdID int,dteDate DATETIME,numPrice DECIMAL (18,2))
    insert into #tmp2 (fkiProdID,dteDate)
    select 1,'2013-01-02'
    union all
    select 1,'2013-01-05'
    union all 
    select 1,'2013-01-09'
    
    update #tmp2
    set numPrice = #tmp.numPrice
    from #tmp inner join #tmp2 on (#tmp.pkiProd = #tmp2.fkiProdID)
    where #tmp2.dteDate = #tmp.dteDate or #tmp.dteDate < #tmp2.dteDate --?
    
    select *from #tmp2
    
    drop table #tmp
    drop table #tmp2



    • Edited by LVE7 Tuesday, November 19, 2013 8:43 AM
    Tuesday, November 19, 2013 8:37 AM

Answers

  • May be the below:

    create table #tmp (pkiProd int,dteDate datetime,numPrice decimal (18,2))
    insert into #tmp select 1,'2013-01-01',1
    union all
    select 1,'2013-01-02',1.5
    union all 
    select 1,'2013-01-03',1.7
    union all 
    select 1,'2013-01-04',1.9
    union all 
    select 1,'2013-01-05',2
    union all 
    select 1,'2013-01-06',2.4
    union all 
    select 1,'2013-01-07',3.4
    
    
    create table #tmp2 (pkiTransID int identity, fkiProdID int,dteDate DATETIME,numPrice DECIMAL (18,2))
    insert into #tmp2 (fkiProdID,dteDate)
    select 1,'2013-01-02'
    union all
    select 1,'2013-01-05'
    union all 
    select 1,'2013-01-09'
    
    
    update A Set A.numPrice = B.numPrice
    From #tmp2 A
    Inner join #tmp 
     B on A.fkiProdID = B.pkiProd and A.dteDate = B.dteDate
    
    ;With cte
    as
    (
    	Select B.pkiProd,B.dteDate MaxDate,B.numPrice ,ROW_NUMBER()Over(partition by B.pkiprod order by B.dtedate desc) Rn 
    	From #tmp2 A
    	Inner join #tmp B on A.fkiProdID = B.pkiProd and A.dteDate>B.dteDate
    	Where A.numPrice is null
    ) update A Set A.numPrice = B.numPrice
    From #tmp2 A
    inner join cte B On A.fkiProdID = B.pkiProd
    where Rn=1 and A.numPrice is null
    
    select *from #tmp2
    
    drop table #tmp
    drop table #tmp2


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by LVE7 Tuesday, November 19, 2013 9:25 AM
    Tuesday, November 19, 2013 9:20 AM

All replies

  • May be the below:

    create table #tmp (pkiProd int,dteDate datetime,numPrice decimal (18,2))
    insert into #tmp select 1,'2013-01-01',1
    union all
    select 1,'2013-01-02',1.5
    union all 
    select 1,'2013-01-03',1.7
    union all 
    select 1,'2013-01-04',1.9
    union all 
    select 1,'2013-01-05',2
    union all 
    select 1,'2013-01-06',2.4
    union all 
    select 1,'2013-01-07',3.4
    
    
    create table #tmp2 (pkiTransID int identity, fkiProdID int,dteDate DATETIME,numPrice DECIMAL (18,2))
    insert into #tmp2 (fkiProdID,dteDate)
    select 1,'2013-01-02'
    union all
    select 1,'2013-01-05'
    union all 
    select 1,'2013-01-09'
    
    
    update A Set A.numPrice = B.numPrice
    From #tmp2 A
    Inner join #tmp 
     B on A.fkiProdID = B.pkiProd and A.dteDate = B.dteDate
    
    ;With cte
    as
    (
    	Select B.pkiProd,B.dteDate MaxDate,B.numPrice ,ROW_NUMBER()Over(partition by B.pkiprod order by B.dtedate desc) Rn 
    	From #tmp2 A
    	Inner join #tmp B on A.fkiProdID = B.pkiProd and A.dteDate>B.dteDate
    	Where A.numPrice is null
    ) update A Set A.numPrice = B.numPrice
    From #tmp2 A
    inner join cte B On A.fkiProdID = B.pkiProd
    where Rn=1 and A.numPrice is null
    
    select *from #tmp2
    
    drop table #tmp
    drop table #tmp2


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by LVE7 Tuesday, November 19, 2013 9:25 AM
    Tuesday, November 19, 2013 9:20 AM
  • LVE7, Use below Code.

    DECLARE @DateVariable DATETIME
    SET @DateVariable = (SELECT MAX(dteDate)FROM #tmp)
    
    UPDATE TT
    SET TT.Price=T.Price
    FROM #tmp T RIGHT JOIN #tmp2 TT ON T.pkiProdID=TT.fkiProdID
    WHERE TT.dteDate=ISNULL(T.dteDate,@DateVariable) 

    Pardon me for any typos.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 19, 2013 9:25 AM
  • Does  your query perform correct data? your query looks ok.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 19, 2013 9:53 AM
    Answerer
  • create table #tmp (pkiProd int,dteDate datetime,numPrice decimal (18,2))
    insert into #tmp select 1,'2013-01-01',1
    union all
    select 1,'2013-01-02',1.5
    union all 
    select 1,'2013-01-03',1.7
    union all 
    select 1,'2013-01-04',1.9
    union all 
    select 1,'2013-01-05',2
    union all 
    select 1,'2013-01-06',2.4
    union all 
    select 1,'2013-01-07',3.4
    create table #tmp2 (pkiTransID int identity, fkiProdID int,dteDate DATETIME,numPrice DECIMAL (18,2))
    insert into #tmp2 (fkiProdID,dteDate)
    select 1,'2013-01-02'
    union all
    select 1,'2013-01-05'
    union all 
    select 1,'2013-01-09'
    declare @maxdt datetime = (Select Max(dteDate) From (select dteDate from  #tmp2 Union select dteDate from  #tmp) t)
     ;WITH mycte AS
    (
    SELECT pkiProd,  min( dteDate ) dteDate   FROM #tmp  group by pkiProd
    UNION ALL
    SELECT  m.pkiProd,  DATEADD(day,1,m.dteDate) eTime  
    FROM mycte m 
    WHERE  m.dteDate< @maxdt ) 
    ,mycte1 as (select m.dteDate, t.numPrice,m.pkiProd from mycte m left join #tmp t On m.dteDate=t.dteDate)
    ,mycte2 as
    (Select pkiProd, dteDate,d.numprice from mycte1 m cross apply (select top 1 numPrice from mycte1 where dteDate<=m.dteDate AND numprice is not null Order by numPrice Desc) d (numprice)
    )
    merge #tmp2 A
    Using mycte2  B on A.fkiProdID = B.pkiProd and A.dteDate = B.dteDate
    WHEN matched Then
    update   Set A.numPrice = B.numPrice;
     
    select *from #tmp2

    Tuesday, November 19, 2013 3:53 PM
    Moderator
  • This is not RDBMS. You are using SQL to write a 1950's punch/magnetic tape application. Your temp tables  mimic decks of punch cards and scratch tapes! Please post full DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You never put the data type in the data element name. That was FORTRAN II and 1960's BASIC. You never encode how is it used locally; that was COBOL and pre-RDBMS. 

    UPDATE.. FROM.. is 1970's Sybase dialect and it does not work. Google it! The results can be unpredictable. 

    Let's use the GTIN for the products. Database programmers look for industry standards and universal identifier, not local names.  Your whole schema is wrong. You are trying to track unit_price over time. 


    CREATE TABLE Unit_Price_History
    (gtin CHAR(15) NOT NULL, 
     price_start_date DATE NOT NULL, 
     price_end_date DATE, --- null is current
     CHECK (price_start_date <= price_end_date),
     PRIMARY KEY (gtin, price_start_date),
     unit_price DECIMAL (18,2) NOT NULL
       CHECK (unit_price >= 0.00));

    Here is the ANSI/ISO syntax for insertion. It rep;aced the old proprietary Sybase notation over decade ago. 

    INSERT INTO Inventory
    ('123456789012345', '2000-01-01', '2013-01-01', 1.00), 
    ('123456789012345', '2013-01-02', '2013-01-02', 1.50), 
    ('123456789012345', '2013-01-03', '2013-01-03', 1.70), 
    ('123456789012345', '2013-01-04', '2013-01-04', 1.90),  
    ('123456789012345', '2013-01-05','2013-01-05', 2.00), 
    ('123456789012345', '2013-01-06', '2013-01-06', 2.40), 
    ('123456789012345', '2013-01-07', NULL, 3.40);

    IDENTITY is how you 1950's punch/magnetic tape programmers fake a record count in SQL. We do not use it. WE HAVE KEYS! Why would a count of the physical insertion attempts to some table on one disk be part of a valid relational data model? All you need is a VIEW and not a scratch tape.  First, this will be handy

    CREATE VIEW Current_Unit_Prices
    AS
    SELECT gtin, price_start_date, unit_price
      FROM Unit_Price_History
     WHERE price_end_date IS NULL;

    for a particular stock date and product, use a query and do not materialize it like a scratch tape or deck of punch cards

    CREATE PROCEDURE Get_Price_by_Date
    (@in_gtin CHAR(15),
      @in_stock_date DATE)
    AS
    SELECT gtin, @in_stock_date AS stock_date, unit_price
      FROM Unit_Price_History
     WHERE gtin = @in_gtin
       AND @in_stock_date
           BETWEEN price_start_date 
               AND COALESCE (price_start_date, CURRENT_TIMESTAMP);


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 19, 2013 7:10 PM