troubleshooting Question

SQL query many-to-1

Avatar of EffinGood
EffinGoodFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
5 Comments1 Solution454 ViewsLast Modified:
Hi all,

I have two tables, tbl1 and tbl2.  Tbl2 is a collection of ids and colors, for example:
colorId | color
1 | Red
2 | Blue
3 |Green
etc.

tbl1 is a table of items, and associated with each item id is a single color id.  For example:

itemId | name | colorId
1 | shirt | 1
2 | cake | 3
3 | umbrella | 2
etc.

Both tables are already populated with data.  Currently, this is a 1 to 1 relationship. Each item is only available in 1 color.  I need to set it up so that each item is available in multiple colors.  I have built tbl3 to allow a 1-to-many relationship going forward:

colorsCollectionId | colorId
1 | 1
1 | 2
2 | 3
2 | 1
etc.

Here's my question: how can I write a SQL query that handles the existing data, and migrates the current values from the colorId field tbl1 to tbl3, and replaces them in tbl1 with a colorsCollectionId?

Thanks for your help!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros