Update Duplicate Records 1

[RJL1]

I have a list of container numbers that from time to time are duplicated. I would like to create an update query that will update each duplicate record by appending each record with a sequential letter to the end of each container number

This is the original data

Container ID 
90122334455
90122334455
90122334455
89330000111
89330000111
89330000111

This is how it would look after updating the duplicate containers

Container ID 
90122334455A
90122334455B
90122334455C
89330000111A
89330000111B

The update letters would need to re-start on change of container ID and will need to update all duplicate containers that do not end with "C" as these indicate a case container.

Any hepl on how this could be done is appreciated

Thanks
RJL

 

[RJL1]

UPDATE

Here is how I fix these today

--run to see if any duplicate containers exist

SELECT container_id, company, warehouse
FROM shipping_container
WHERE right(container_id,1) <> 'C'
GROUP by container_id, company, warehouse
HAVING count(container_id) >1

This will return

Container_ID      Company      Warehouse
91000727346608    019          014

The I wil ltake the container ID and run this query

--to look at a certain container

select status, logistics_unit,quantity, lot,*
from shipping_container
where container_id ='91000727346608'

This will return

Status   Container_ID     Internal Container Num
300      91000727346608   6642351
300      91000727346608   6644693

Finally I run this based on each infividual Internal Container Num and chnage the letter to the next one for each container

--to update a container

update shipping_container set container_id = container_id + 'A'
where   container_id = '91000714072035'
and status < 900
and internal_container_num = 6636568

I like to develop the query so it finds the duplicates and updates them automatically so I can scheduel this as part of pro-active mainteance

Thanks
RJL

 

[hopper44]

Try something along the following lines:

DECLARE @tmptable TABLE (CID VARCHAR(30), ROWNO INT, internal_container_num VARCHAR(30))

INSERT INTO @tmptable
select s.container_id, ROW_NUMBER() OVER ( partition by s.container_id order by s.container_id) AS ROW_NUMBER, internal_container_num
FROM shipping_container s
INNER JOIN
(select container_id from shipping_container
group by container_id having COUNT(1) > 1) s2
ON s2.container_id = s.container_id
where status < 900

UPDATE shipping_container
set container_id =
( Case T.ROWNO
WHEN 1 THEN container_id + CHAR(65) -- append 'A'
WHEN 2 THEN container_id + CHAR(66) -- append 'B'
WHEN 3 THEN container_id + CHAR(67) -- append 'C'
WHEN 4 THEN container_id + CHAR(68) -- append 'D'
WHEN 5 THEN container_id + CHAR(69) -- append 'E'
WHEN 6 THEN container_id + CHAR(70) -- append 'F'
WHEN 7 THEN container_id + CHAR(71) -- append 'G'
WHEN 8 THEN container_id + CHAR(72) -- append 'H'
WHEN 9 THEN container_id + CHAR(73) -- append 'I'
END
)
FROM shipping_container s
INNER JOIN @tmptable T
ON s.internal_container_num = T.internal_container_num

 

[RJL1]

Thanks hopper44. This worked like a charm. Did just what we needed. It tok me a little time to understand the script but now is working great

Thanks