SQL Update

container_id	part_code	pallet_id
768174	PUR12630	00101780078697243539
768174	PUR14842	00101780078697243539
768174	PUR15467	00101780078697243539
768174	PUR12088	00101780078697243546
768174	PUR14644	00101780078697243546
768174	PUR15859	00101780078697243546
768174	PUR15862	00101780078697243546

Open in new window


Can someone point me in the right direction.  We have some files that populate the system that come in with the same container_id with the same pallet_id.  What I am looking for is a away to automate a fix in the sql table as our ERP system doesn't allow this to happen and I can't change how this comes from the source. How would I add a number on the end to make this a unique pallet_id for that particular container_id.  

Example (add a number)
container_id	part_code	pallet_id
768174	PUR12630	001017800786972435391
768174	PUR14842	001017800786972435392
768174	PUR15467	001017800786972435393
768174	PUR12088	001017800786972435461
768174	PUR14644	001017800786972435462
768174	PUR15859	001017800786972435463
768174	PUR15862	001017800786972435464

Open in new window

gpsdhAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
give a try if you are working in 2005 or later.
;WITH CTE AS (
SELECT container_id,part_code,pallet_id,
       ROW_NUMBER() OVER (PARTITION BY container_id,pallet_id ORDER BY part_code) rn
  FROM YourTable)
SELECT container_id,part_code,pallet_id+CONVERT(VARCHAR,rn) pallet_id
  FROM CTE

Open in new window

0
 
JimFiveConnect With a Mentor Commented:
select containerID, PartCode, palletnumber + CAST(ROWID() OVER(ContainerID ORDER BY PartCode) as varchar(2)
FROM TABLE
0
 
gpsdhAuthor Commented:
I am getting this error when I run that.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.

RowID is not a recognized built in function name  --This shows up when I put my mouse over RowID in the query.


select container_ID, Part_Code, pallet_id + CAST(ROWID() OVER(Container_ID ORDER BY Part_Code) as varchar(2)
FROM edi856in


SELECT TOP 5 container_id,part_code,pallet_id, rowid

FROM edi856in


container_id	part_code	pallet_id	rowid
2462024	4538	6007960038-1	103836
2462024	7531	6007960038-1	103837
2462024	7919	6007960038-1	103838
2462024	8556	6007960038-1	103839
2462024	8602	6007960038-1	103840

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
SharathData EngineerCommented:
What is your SQL Server version?
0
 
gpsdhAuthor Commented:
I have never used CTE, so if I wanted to update it instead of select it what would I need to do?
0
 
gpsdhAuthor Commented:
This was my solution.  Thanks.

  WITH PID AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY container_id,pallet_id ORDER BY part_code) AS RN
  FROM edi856in
)
UPDATE PID
SET pallet_id = pallet_id + CAST(RN AS VARCHAR(255))
WHERE RN > 1;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.