Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Update

Posted on 2013-12-10
6
Medium Priority
?
168 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
  • 3
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 1000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 41

Accepted Solution

by:
Sharath earned 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

972 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