update oldID field based on fields in the same tab

Question

I need help with the following query.

create table #table1
(id int not null primary key identity,
customer_name varchar(25),
usage float,
oldID int null
)


insert into #table1 values('ABC',46.5,null)
insert into #table1 values('ABC',46.5,null)
insert into #table1 values('DEF',36.8,null)
insert into #table1 values('XYZ',50.1,null)
insert into #table1 values('DEF',36.8,null)
insert into #table1 values('XYZ',50.1,null)

select * from #table1

I want my table to be updated like this

id          customer_name             usage                  oldID
----------- ------------------------- ---------------------- -----------
1           ABC                       46.5                   NULL
2           ABC                       46.5                   1
3           DEF                       36.8                   NULL
4           XYZ                       50.1                   NULL
5           DEF                       36.8                   3
6           XYZ                       50.1                   4

1. The two records with the same name and usage means the later record was renewed.
2. In the new record the oldID field should point to its old record (ID).

Although in my actual table, I have a bunch of date fields which I probably can use but this would help me for now.

Answer 1

Try this using a CTE:

;WITH data AS
(
    SELECT 
        id, customer_name,
        OldID = (SELECT MIN(id) FROM #table1 t2 WHERE t2.customer_name = t.customer_name)
    FROM #table1 t
)
UPDATE #table1
SET OldID = data.OldID
FROM Data
WHERE 
    data.customer_Name = #table1.customer_name
    AND #table1.ID <> data.oldid

select * from #table1

The Data CTE basically just determines the minimum ID for each customer, and if that customer's ID isn't that minimum ID, then OldID is set to that ID value.

When I run this, I get a resulting output:

id  customer_name   usage   oldID
 1   ABC            46.5    NULL
 2   ABC            46.5    1
 3   DEF            36.8    NULL
 4   XYZ            50.1    NULL
 5   DEF            36.8    3
 6   XYZ            50.1    4

Answer 2

With cte, without subquerys, updating only customers with several rows:

with cte as (
   select customer_name, min( id ) as id
   from #table1
   group by customer_name
   having count(*) > 1
)
update #table1 
 set oldID = cte.id
 from cte 
 where #table1.customer_name = cte.customer_name
 and #table1.id != cte.id