Rick
asked on
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'
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'
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
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
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
update with join: syntax explained here:
https://www.experts-exchange.com/A_1517.html
https://www.experts-exchange.com/A_1517.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Update Tbl1
Set Col3 = (
select top(1) Tbl2.Col3
Where Tbl1.Col1 = Tbl2.Col1
Order by Tbl2.Col4 desc)
Set Col3 = (
select top(1) Tbl2.Col3
Where Tbl1.Col1 = Tbl2.Col1
Order by Tbl2.Col4 desc)
An expensive way to write top 1
May even cause a table scan to process the row numbering...
May even cause a table scan to process the row numbering...
ASKER
I meant to pick MS SQL Server, not MySQL Server...