Solved

SQL Update

Posted on 2013-12-10
6
160 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 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 40

Expert Comment

by:Sharath
ID: 39710300
What is your SQL Server version?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 40

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now