Home » SQL & PL/SQL » SQL & PL/SQL » Updating a duplicate Primarykey (Oracle 10g)
Updating a duplicate Primarykey [message #567962] Mon, 08 October 2012 09:28 Go to next message
theladyd
Messages: 13
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 #567963 is a reply to message #567962] Mon, 08 October 2012 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
> 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.
so why do you need to change anything else.
Just enable the PK again.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Re: Updating a duplicate Primarykey [message #567964 is a reply to message #567963] Mon, 08 October 2012 09:47 Go to previous messageGo to next message
theladyd
Messages: 13
Registered: September 2008
Junior Member
I still have duplicate primarykeys, but not duplicate records. I want to update the primarykey to the next increment before resetting the constraint.
Re: Updating a duplicate Primarykey [message #567967 is a reply to message #567964] Mon, 08 October 2012 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Valid SQL statements are described in Database SQL Reference.

We have not your tables so can't provide a valid solution but id could be something like:
UPDATE myTABLE a
SET (PM_PM_KEY, PM_VERNO)=
    (select MAX(PM_KEY)+1, MAX(PM_VERNO)+1
     from mytable b 
     WHERE TBLDATE= MAX(TBLDATE) 
       and b.PCN = a.pcn
/

If 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
Re: Updating a duplicate Primarykey [message #567969 is a reply to message #567962] Mon, 08 October 2012 10:16 Go to previous messageGo to next message
flyboy
Messages: 1770
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 Go to previous messageGo to next message
theladyd
Messages: 13
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 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 08 October 2012 17:08
If 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

Previous Topic: Convert date to numeric
Next Topic: Best logic to iterate
Goto Forum:
  


Current Time: Fri Aug 22 12:47:12 CDT 2014

Total time taken to generate the page: 0.05131 seconds