Solved

SQL Update

Posted on 2013-12-10
6
165 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:gpsdh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 250 total points
ID: 39709972
select containerID, PartCode, palletnumber + CAST(ROWID() OVER(ContainerID ORDER BY PartCode) as varchar(2)
FROM TABLE
0
 

Author Comment

by:gpsdh
ID: 39710108
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
 
LVL 41

Expert Comment

by:Sharath
ID: 39710300
What is your SQL Server version?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 39710307
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
 

Author Comment

by:gpsdh
ID: 39712708
I have never used CTE, so if I wanted to update it instead of select it what would I need to do?
0
 

Author Closing Comment

by:gpsdh
ID: 39714667
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question