Duplicate primarykeys - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate primarykeys

Author

Topic

db17
Starting Member

2 Posts

Posted - 2012-10-05 : 19:06:31

PM_KEY PCN PM_VERNO
20 137 0
21 137 1
22 137 2
23 137 3
22 137 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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-05 : 21:19:21

SQLTeam is a Microsoft SQL Server website. You can get help with Oracle questions at http://dbforums.com/