Need help with update query - 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

Need help with update query

Author

Topic

kancharlasoumya
Starting Member

6 Posts

Posted - 2008-09-10 : 10:04:37

I am doing update on a table but it doesnt allow me to do that ,it says Violation of PRIMARY KEY constraint 'pkName_Security_GroupsSECURITY'. Cannot insert duplicate key in object 'Name_Security_Groups'.

update NSG1

set NSG1.SECURITY_group=NSG.SECURITY_GROUP

from NAME n1

inner join NAME_SECURITY_GROUPS NSG1 on NSG1.id=n1.id

inner join name n on n.id=n1.CO_ID

inner join NAME_SECURITY_GROUPS NSG on NSG.id=n.id

where n1.CO_ID=n.id

Name_SECURITY_GROUPS has the folllowing columns
ID -- Primar key
SECURITY_GROUP-- Primary key

Please help me out in solving this isse.

Soumya

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-10 : 10:13:07

Easy. DO NOT update primary keys. They are primary keys for a reason!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

kancharlasoumya
Starting Member

6 Posts

Posted - 2008-09-10 : 10:52:53

But when i was doing single record update i was able to update security_groups which is a primary key to the table NAME_SECURITY_GROUPS

Soumya

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 10:55:38

Because had the luck to change the value to another unique value.

E 12°55'05.63"
N 56°04'39.26"

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-09-10 : 11:28:28

In the [NAME] table a single CO_ID could have multiple [id]s which would cause the PK violation.
If you REALLY want to do this you will need to follow some sort of
"Put new values in a temp table", "Delete old values", "Insert new values" strategy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources