| Updating a duplicate Primarykey [message #567962] |
Mon, 08 October 2012 09:28  |
theladyd
Messages: 11
Registered: September 2008
|
Junior Member |
|
|
PM_KEY.. PCN....... JOBNO...PM_VERNO
20......... 137....... XX23..... 0
21......... 137....... XX24..... 1
22......... 137....... XX17..... 2
23......... 137....... XX81..... 3
22......... 137....... XX90..... 2
I have a dilemma......the constraint was disable somehow in my table of about 900,000 records which allowed the insertion on duplicate primary keys as well as duplicate records. I've managed to get rid of the duplicate records, but I haven't quite figured out how to update the primary key and version number. I've tried the following but
UPDATE TABLE
SET PM_PM_KEY=(MAX(PM_KEY)+1), PM_VERNO=(MAX(PM_VERNO)+1)
WHERE TBLDATE= MAX(TBLDATE)
ORDER BY TBLDATE ASC
GROUP BY PCN;
The query is failing with
Error at Command Line:2 Column:19
Error report:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
I will appreciate any and all help
|
|
|
|
|
|
|
|
|
|
| Re: Updating a duplicate Primarykey [message #567969 is a reply to message #567962] |
Mon, 08 October 2012 10:16  |
flyboy
Messages: 1670
Registered: November 2006
|
Senior Member |
|
|
Assuming, that there are no duplicates on (PCN,TBLDATE) (by the way, the second column is not present anywhere else in your post) and you want to update some columns of the rows with the grestest TBLDATE for given PCN with greatest values of those columns for given PCN + 1, it could be something like this:
UPDATE TABLE t1
SET PM_PM_KEY,PM_VERNO=(select MAX(PM_KEY)+1, MAX(PM_VERNO)+1
FROM TABLE t2
WHERE t1.pcn = t2.pcn)
WHERE TBLDATE=(select MAX(TBLDATE)
FROM TABLE t2
WHERE t1.pcn = t2.pcn);
As you did not any testcase - CREATE TABLE statements for table structure, INSERT statements for sample data, expected result set and exact rules for achieving it - I could not test it. Note, that it will increase values of PM_PM_KEY and PM_VERNO columns each run.
Also, you should get familiar with SQL syntax. It is described in SQL Language Reference book, which is available with other documentation e.g. online on http://tahiti.oracle.com/
|
|
|
|
| Re: Updating a duplicate Primarykey [message #568513 is a reply to message #567969] |
Fri, 12 October 2012 17:10 |
theladyd
Messages: 11
Registered: September 2008
|
Junior Member |
|
|
Thanks all, your suggestions help me to resolve the issue for the most part. As I am in a crunch and needed a quick and simple solution, I reordered the verno, substracting the old verno and added the new verno because the duplicate pm_key was not always the max. However, I did discover that I still have about 500 duplicate pm_keys, no duplicate records. I agree, flyboy, that I should get familiar with SQL syntax, and I do research and read as much as possible. But, I am in a crunch and really, really appreciate your response to my post. This is how the 500 or more duplicates stack up:
PM_KEY. PCN.. JOBNO.. TBLDATE.. VERNO
19710.. 7441.. XX304.. 05-JUN-08.. 0
19711.. 7441.. XX304.. 09-JUN-08.. 1
19712.. 7441.. XX304.. 07-OCT-08.. 2
19713.. 7441.. XX304.. 23-FEB-09.. 3
19714.. 7441.. XX304.. 27-SEP-12.. 4
19710.. 792657. RR015.. 16-MAY-07.. 0
19711.. 792657. RR015.. 26-JUN-08.. 1
I want to take the pcn with the least versions and change the pm_key to the next increment in the table.
I am researching and trying to put together the sql to resolve this, but I know that there are experts out there that can solve this at a snap of the finger. Thank you very much and I appreciate your help.
|
|
|
|
| Re: Updating a duplicate Primarykey [message #568515 is a reply to message #568513] |
Sat, 13 October 2012 00:02 |
 |
Michel Cadot
Messages: 54167
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Mon, 08 October 2012 17:08If this is not what you want, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
Regards
Michel
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
