CODEWITHSUNDEEP
sqlsql-server
Benny Ae
Benny Ae

Reputation: 2016

SQL update other rows by one latest date row

this is a MS-SQL

there are many rows of data duplicated , some of them get the latest update, some not. I want to update those old data with ones get latest info.

from:
    orderNum      itemID          orderTime                        desc
    7247168         101         2013-08-11 09:51:39.20             desc_101_cc
    102594          101         2012-09-26 21:17:50.44             desc_101_aaa
    631595          101         2014-03-11 19:51:29.40             desc_101_ddd
    1157428         235         2014-03-01 10:16:42.43             desc_235_8
    7212306         235         2014-03-14 11:26:51.29             desc_235_2
    100611          235         2014-03-21 20:23:43.03             desc_235_2

To:

orderNum      itemID          orderTime                        desc
7247168         101         2013-08-11 09:51:39.20             desc_101_ddd
102594          101         2012-09-26 21:17:50.44             desc_101_ddd
631595          101         2014-03-11 19:51:29.40             desc_101_ddd
1157428         235         2014-03-01 10:16:42.43             desc_235_2
7212306         235         2014-03-14 11:26:51.29             desc_235_2
100611          235         2014-03-21 20:23:43.03             desc_235_2

I want to use the max(orderTime) to get the latest edition of desc then use it to update other desc

That means i like to use the orderTime to tell which desc is the latest then update other desc

The only column needs to be updated is the desc

Please help me with this SQL

Upvotes: 0

Views: 55

Answers (3)

SoulTrain
SoulTrain

Reputation: 1904

Try this...I am using ROW_NUMBER() to pick the latest updated record and then setting the desc columns for others

WITH CTE
AS (
    SELECT *
        , ROW_NUMBER() OVER (partition by itemid ORDER BY ordertime desc) ROWNUM
    FROM Your_table_name    
    )
UPDATE A
SET desc = CTE.desc
FROM Your_table_name A
INNER JOIN CTE ON A.itemid = CTE.itemid
WHERE CTE.ROWNUM=1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you are using SQL Server 2012, you can do this with last_value:

with toupdate as (
      select t.*,
             last_value("desc") over (partition by itemID order by orderTime) as lastdesc
      from table t
)
update toupdate
    set "desc" = lastdesc;

If you are not using SQL Server 2012, you an emulate this with a correlated subquery:

with toupdate as (
      select t.*,
             (select top 1 "desc"
              from table t2
              where t2.itemId = t.itemId
              order by orderTime desc
             ) as lastdesc
      from table t
)
update toupdate
    set "desc" = lastdesc;

Upvotes: 1

outis nihil
outis nihil

Reputation: 736

Something like this (won't work in SQL Server 2000 or earlier)? Don't try this on a production table; make a temporary copy table to try it.

;WITH MaxT AS (
    SELECT 
        itemID
        ,maxOrderTime = MAX(orderTime) 
    FROM 
        myTable
    ),
 MaxTDesc AS (
    SELECT 
        itemID
        ,desc 
    FROM 
        myTable MaxTDesc
        ,MaxT
    WHERE 
        MaxTDesc.ItemID = MaxT.ItemID 
        AND MaxTDesc.orderTime = MaxT.maxOrderTime
    )
 UPDATE 
    mt 
 SET 
    mt.desc = MaxTDesc.desc
 FROM 
    myTable mt, MaxT 
 WHERE 
    mt.itemID = MaxTDesc.itemID

Upvotes: 1

Related Questions