Solved

SQL Update

Posted on 2013-12-10
6
166 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
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

617 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