| Author |
Topic |
|
fiendz138
Starting Member
5 Posts |
 Posted - 2013-12-27 : 11:56:49
|
| I have been tasked with cleaning up our call tracking database.I am looking to run this update:UPDATE CallLog set extcaltyp=replace(extcaltyp, 'SW - MS ACCESS', 'MS Office')UPDATE CallLog set extcaltyp=replace(extcaltyp, 'SW - MS EXCEL', 'MS Office')I get an error that the record already exists when the 2nd statement runs. Is there a way I can get around this so that I can change these to the same value?Thanks in advance for any help you can provide. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-27 : 12:36:58
|
| Are you getting the error through Management Studio? Are you using a New Query window? If not, you should be.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
fiendz138
Starting Member
5 Posts |
Posted - 2013-12-27 : 13:26:20
|
| Yes. I am using the new query window. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fiendz138
Starting Member
5 Posts |
Posted - 2013-12-27 : 13:38:46
|
| I'm going to have to run the update again to get you the exact error. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-28 : 03:35:03
|
| Is there a UNIQUE index/constraint on extcaltyp?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-28 : 09:12:46
|
| [code]UPDATE CL1set extcaltyp=replace(extcaltyp, 'SW - MS EXCEL', 'MS Office')FROM CallLog AS CL1WHERE NOT EXISTS( SELECT * FROM CallLog AS CL2 WHERE CL2.MyPKey <> CL1.MyPKey ... any additional PKey fields here ... AND CL2.OtherKey = CL1.OtherKey ... any additional additional key fields here ... AND CL2.extcaltyp = replace(CL1.extcaltyp, 'SW - MS EXCEL', 'MS Office'))[/code]then[code]SELECT Col1, Col2, ...FROM CallLogWHERE extcaltyp LIKE '%SW - MS EXCEL%'[/code]to see any rows that remain that did NOT get converted. Those records clash. |
|
|
|
fiendz138
Starting Member
5 Posts |
Posted - 2013-12-30 : 23:28:51
|
| All,This is the error I am getting: Msg 2627, Level 14, State 1, Line 147Violation of PRIMARY KEY constraint 'PK_extcalltp_Extcaltyp'. Cannot insert duplicate key in object 'dbo.extcalltp'. The duplicate key value is (MS Office).Kristen,I am kind of new to SQL. Would your update statement allow me to change the records to MS Office even though it already exists?The statement has been terminated |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-12-31 : 04:07:39
|
The error message says it all. You are not allowed to have duplicate values in the column.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-31 : 10:25:58
|
quote:
Originally posted by fiendz138
All,This is the error I am getting: Msg 2627, Level 14, State 1, Line 147Violation of PRIMARY KEY constraint 'PK_extcalltp_Extcaltyp'. Cannot insert duplicate key in object 'dbo.extcalltp'. The duplicate key value is (MS Office).Kristen,I am kind of new to SQL. Would your update statement allow me to change the records to MS Office even though it already exists?The statement has been terminated
Either redefine your primary key to include more columns which will provide a unique combination or drop the primary key itself before you do the update------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
fiendz138
Starting Member
5 Posts |
Posted - 2013-12-31 : 13:15:43
|
| How would I drop the primary key? And would I need to do that each time I want to update with a duplicate value? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-31 : 13:32:30
|
quote:
Originally posted by fiendz138
How would I drop the primary key? And would I need to do that each time I want to update with a duplicate value?
use ALTER TABLE...DROP CONSTRAINT to drop the PK. Then recreate it with column combination as per your business relevanceThe change is one time. Once you redefine primary key based on group of columns you'll be able to do updation thereafter------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-02 : 07:25:45
|
quote:
Originally posted by fiendz138
All,Would your update statement allow me to change the records to MS Office even though it already exists?
Yes.It will convert anything to MS OFFICE if that does not already exist, and then the second query will show you anything that it could NOT convert to MS OFFIce because it already exists.I strongly advise you NOT to drop the primary key / unique constraint. It is presumably there to stop you have two entries of MS OFFICE for a single entity.If you are changing MS ACCESS and MS EXCEL to MS OFFICE then it seems highly likely to be that someone will have both ... and thus your change to MS OFFICE will attempt to give them two records with the same name, whereas I expect what you want is a single such record.So my plan would be:Change MS ACCESS to MS OFFICE where there is not existing record of MS OFFICE for that entity.Delete MS ACCESS records that are then left behind (probably best to check that you only do that where there EXISTS an MS OFFICE record - otherwise someone else might have added a new MS ACCESS record in the meantime)Then do the same converting MS EXCEL to MS OFFICE and deleting any MS EXCEL that are left behind (where MS OFFICE already exists) |
|
|
|
|
Update/Replace Statement