• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

SQL - Update from top 1 in another table

Hello,

I need to Update all values in Tbl1.Col3 (nvarchar)
From Tbl2.Col3
Where Tbl1.Col1 = Tbl2.Col1
And Tbl.Col4 is the most recent (Date Desc)


Tbl1:                                
-------------------------------
SKU     DESC.     STATUS
Abc     None             *
Def      None             **
                                 
                                 
Tbl2:                                                                            
-------------------------------                                          
SKU     DESC.     STATUS     DATE                              
Abc     None       A                20101023 12:03            
Abc     None       B                20101101 8:16              
Def     None       D                20101029 5:15
Def     None       B                  20101028 10:10            

                                                                                   

* This would be Set to 'B'    
** This would be Set to 'D'    
0
Rick
Asked:
Rick
1 Solution
 
RickAuthor Commented:
Oops, wrong Zone...
I meant to pick MS SQL Server, not MySQL Server...
0
 
hyphenpipeCommented:
with updateCTE
as
(
select sku, status, row=row_number() over (partition by status order by date desc)
from Tbal2
)
update Tbl1 set status = u.status from Tbl1 t, updateCTE u
 where t.sku = u.sku
 and u.row = 1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update with join: syntax explained here:
http://www.experts-exchange.com/A_1517.html
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
hyphenpipeCommented:
Slight modification:

with updateCTE
as
(
select sku, status, row=row_number() over (partition by sku order by date desc)
from Tbl2
)
update Tbl1 set status = u.status from Tbl1 t, updateCTE u
 where t.sku = u.sku
 and u.row = 1
0
 
cyberkiwiCommented:
Update Tbl1
Set Col3 = (
      select top(1) Tbl2.Col3
      Where Tbl1.Col1 = Tbl2.Col1
      Order by Tbl2.Col4 desc)
0
 
cyberkiwiCommented:
An expensive way to write top 1
May even cause a table scan to process the row numbering...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now