# 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 Tuesday, November 19, 2013 8:43 AM
Tuesday, November 19, 2013 8:37 AM

• 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```

• Marked as answer by 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```

• Marked as answer by 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.

Tuesday, November 19, 2013 9:25 AM

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
• ```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
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
• 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