Consolidating values in a junction table

Posted by senloe on Stack Overflow See other posts from Stack Overflow or by senloe
Published on 2010-04-20T20:24:09Z Indexed on 2010/04/21 18:03 UTC
Read the original article Hit count: 271

Filed under:

tsql

|

unique-constraint

|

sql-server

|

common-table-expression

I have the following schema:

Parcels       Segments        SegmentsParcels
=========     ==========      =================
ParcelID      SegmentID       ParcelID
...           Name            SegmentID
              ...             id

A user of the data wants to consolidate Segments.Names and gave me a list of current Segment.Names mapped to new Segment.Names (all of which currently exist).

So now I have this list in a temporary table with the currentID and newID to map to.

What I want to do is update the SegmentID in SegmentsParcels based on this map. I could use the statement:

update SegmentParcels set segmentID = [newID] from newsegments where segmentID = currentid

but this will create some duplicates I have a unique constraint on ParcelID and SegmentID in SegmentParcels.

What is the best way to go about this? I considered removing the constraint and then dealing with removing the duplicates (which I did at one point and could probably do again) but I was hoping there was a simpler way.

© Stack Overflow or respective owner

Related posts about tsql

TSQL Challenge 30 - A Matrix Transposition challenge in TSQL

as seen on SQL Server Central - Search for 'SQL Server Central'
This is a matrix transposition challenge. Your job is to change the position of the numbres in a 5x5 matrix. SQL Server monitoring made easy "Keeping an eye on our many SQL Server instances is much easier with SQL Response." Mike Lile.Download a free trial of SQL Response… >>> More
Will TSQL become useless because of new ORMs? [closed]

as seen on Programmers - Search for 'Programmers'
By introducing LINQ to SQL, I found myself and my .NET developer colleagues gradually moving from TSQL to C# to create queries on the database. Entity Framework made that shift almost permanent. Now it's nearly 2 years that I use LINQ to SQL and LINQ to Entities and haven't used TSQL that much. Yesterday… >>> More
Issue Creating SQL Login for AppPoolIdentity on Windows Server 2008

as seen on Johnny Coder - Search for 'Johnny Coder'
IIS7 introduced the option to run your application pool as AppPoolIdentity. With the release of IIS7.5, AppPoolIdentity was promoted to the default option. You see this change if you’re running Windows 7 or Windows Server 2008 R2. On my Windows 7 machine, I’m able to define my Application… >>> More
The best, in the West

as seen on Simple Talk - Search for 'Simple Talk'
As many of you know, I run the SQL South West user group and we are currently in full flow preparing to stage the UK’s second SQL Saturday. The SQL Saturday spotlight is going to fall on Exeter in March 2013. We have full-day session on Friday 8th with some truly amazing speakers giving their… >>> More
Something for the weekend - Whats the most complex query?

as seen on SQL Blogcasts - Search for 'SQL Blogcasts'
Whenever I teach about SQL Server performance tuning I try can get across the message that there is no such thing as a table. Does that sound odd, well it isn't, trust me. Rather than tables you need to consider structures. You have 1. Heaps 2. Indexes (b-trees) Some people split indexes… >>> More

Related posts about unique-constraint

LINQ To SQL ignore unique constraint exception and continue

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a single table in a database called Users Users ------ ID (PK, Identity) Username (Unique Index) I have setup a unique index on the Username table to prevent duplicates. I am then enumerating through a collection and creating a new user in the database for each item. What I want to do… >>> More
UniqueConstraint in EmbeddedConfiguration

as seen on Stack Overflow - Search for 'Stack Overflow'
I just started using db4o on C#, and I'm having trouble setting the UniqueConstraint on the DB.. here's the db4o configuration static IObjectContainer db = Db4oEmbedded.OpenFile(dbase.Configuration(), "data.db4o"); static IEmbeddedConfiguration Configuration() { IEmbeddedConfiguration dbConfig… >>> More
mysql unique-constraint

as seen on Stack Overflow - Search for 'Stack Overflow'
I have two tables -- Variables (id, name) and Variable_Entries (id, var_id, value). I want each variable to have a unique set of entries. If I make the value entry unique then a different variable won't be able to have that same value which is not right. Is there some way to make the value column… >>> More
Grails- Unique constraint

as seen on Stack Overflow - Search for 'Stack Overflow'
How can I basically carry out a unique constraint on a string data-type field. class User{ String username String Email static hasMany = [roles:Roles] static constraints = { Email(email:true) username(unique:true) } Is there any simple way to implement username(unique:true) or I will… >>> More
Unique Constraint Nhibernate.

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a object with a Nhibernate mapping that has a surrogate ID and a natual ID. Since of cource the natural ID is uniquely constrained a insert query will fail if the object is already in the database with the same natural ID. My solution for this has been to manually check to see if natural… >>> More