473,699 Members | 2,600 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

UPDATE/INSERT to make One-to-Many table become One-to-One

I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.

I need to salvage the records from the many table and without going
into detail, one of the reasons I can't do the opposite as
there are records in the ONE table that I need to keep even if they
don't have any child records in the MANY table.

Below I created the code to create the sample tables:

1- tblProducts is the ONE side table
2- tblProductDetai ls is the MANY side table
3- tblProductsResu lt is the RESULT I expect to get after running
some T-SQL code
4- tblProductCompo nents is another MANY side table to tblProducts
5- tblProductCompo nentsResult is the RESULT I expect to get...

Some of the points to consider:
6- Normally all UniqueID columns are to be IDENTITY. For
this sample i am entering the UniqueID values myself.
7- I don't want to create new tables like tblProductsResu lt
and tblProductCompo nentsResult. I want to update the real tables.
I have created the tblxxxResult tables only for this post.
8- The goal is to update the name of the Product by giving it the
name of the first matching Name from tblProductDetai ls.
9- If there are more than one entry in tblProductDetai ls for each
Product, then I need to create new Products inheriting the original
Product's information including its child records from tblProductCompo nents.

If you run the code and open the tables it will be much clearer
to visually see what I want to achieve.

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO

CREATE TABLE [dbo].[tblProducts] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProducts VALUES (1, 'ABC', 55)
INSERT INTO tblProducts VALUES (2, 'DEF', 66)
INSERT INTO tblProducts VALUES (3, 'GHI', 77)
INSERT INTO tblProducts VALUES (4, 'JKL', 88)

CREATE TABLE [dbo].[tblProductDetai ls] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)

CREATE TABLE [dbo].[tblProductCompo nents] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')

CREATE TABLE [dbo].[tblProductCompo nentsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (11, 5, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (12, 6, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (13, 7, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (14, 7, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (15, 8, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (16, 8, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (17, 9, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (18, 9, 'JKLb')
CREATE TABLE [dbo].[tblProductsResu lt] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
INSERT INTO tblProductsResu lt VALUES (5, 'GHI2', 77)
INSERT INTO tblProductsResu lt VALUES (6, 'GHI3', 77)
INSERT INTO tblProductsResu lt VALUES (7, 'JKL2', 88)
INSERT INTO tblProductsResu lt VALUES (8, 'JKL3', 88)
INSERT INTO tblProductsResu lt VALUES (9, 'JKL4', 88)
I appreciate your assistance on this.
Thank you very much
Jul 23 '05 #1
14 4288
Hi

You can rename your table tblproducts using sp_rename then use something
like:

INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
select (SELECT COUNT(*)
from [dbo].[oldtblProducts] Q
JOIN [dbo].tblProductDeta ils E ON E.[ProductID] = Q.[UniqueID]
WHERE D.[ProductID] > E.[ProductID]
OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS UniqueID,
D.Name,P.TagNo
from [dbo].[oldtblProducts] P
JOIN [dbo].tblProductDeta ils D ON D.[ProductID] = P.[UniqueID]

John

"serge" <se****@nospam. ehmail.com> wrote in message
news:mN******** *************@w agner.videotron .net...
I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.

I need to salvage the records from the many table and without going
into detail, one of the reasons I can't do the opposite as
there are records in the ONE table that I need to keep even if they
don't have any child records in the MANY table.

Below I created the code to create the sample tables:

1- tblProducts is the ONE side table
2- tblProductDetai ls is the MANY side table
3- tblProductsResu lt is the RESULT I expect to get after running
some T-SQL code
4- tblProductCompo nents is another MANY side table to tblProducts
5- tblProductCompo nentsResult is the RESULT I expect to get...

Some of the points to consider:
6- Normally all UniqueID columns are to be IDENTITY. For
this sample i am entering the UniqueID values myself.
7- I don't want to create new tables like tblProductsResu lt
and tblProductCompo nentsResult. I want to update the real tables.
I have created the tblxxxResult tables only for this post.
8- The goal is to update the name of the Product by giving it the
name of the first matching Name from tblProductDetai ls.
9- If there are more than one entry in tblProductDetai ls for each
Product, then I need to create new Products inheriting the original
Product's information including its child records from
tblProductCompo nents.

If you run the code and open the tables it will be much clearer
to visually see what I want to achieve.

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO

CREATE TABLE [dbo].[tblProducts] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProducts VALUES (1, 'ABC', 55)
INSERT INTO tblProducts VALUES (2, 'DEF', 66)
INSERT INTO tblProducts VALUES (3, 'GHI', 77)
INSERT INTO tblProducts VALUES (4, 'JKL', 88)

CREATE TABLE [dbo].[tblProductDetai ls] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)

CREATE TABLE [dbo].[tblProductCompo nents] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')

CREATE TABLE [dbo].[tblProductCompo nentsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (11, 5, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (12, 6, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (13, 7, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (14, 7, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (15, 8, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (16, 8, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (17, 9, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (18, 9, 'JKLb')
CREATE TABLE [dbo].[tblProductsResu lt] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
INSERT INTO tblProductsResu lt VALUES (5, 'GHI2', 77)
INSERT INTO tblProductsResu lt VALUES (6, 'GHI3', 77)
INSERT INTO tblProductsResu lt VALUES (7, 'JKL2', 88)
INSERT INTO tblProductsResu lt VALUES (8, 'JKL3', 88)
INSERT INTO tblProductsResu lt VALUES (9, 'JKL4', 88)
I appreciate your assistance on this.
Thank you very much

Jul 23 '05 #2
Hi John,

Thanks for the code.

I ran your code and even though the code looks very nice (a single Insert
statement), it doesn't work in my case. If you compare my tblProductsResu lt
and the result generated by your code, the UniqueIDs are values that I can't
modify for the already existing records in tblProducts because they are
referenced
in other tables. My tblProducts UniqueID 4 is "JKL", after running your code
the
UniqueID 4 is "GHI2" which would cause problems for me.

I also completely forgot to mention a bigger problem in my original post.
I would also need to update the ProductID values in tblProductDetai ls for
all
the records that are being created in tblProducts. So everytime a new record
is created in tblProducts I will need to get the new Identity value of
tblProducts
and update the ProductID in tblProductDetai ls.

I also just noticed the code I had originally posted doesn't have the
product
'MNO' for my sample example.

Here's the whole new code again if anyone is interested to help me out.
I thought there could be a way to do this using a few update and insert
statements.
I suspect now that maybe it is possible but much harder to write than to
write
some type of a looping through the records one by one cursor and do
UPDATE or INSERT statements one record at a time.

Here's the current code I have for the sample records:

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO

CREATE TABLE [dbo].[tblProducts] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProducts VALUES (1, 'ABC', 55)
INSERT INTO tblProducts VALUES (2, 'DEF', 66)
INSERT INTO tblProducts VALUES (3, 'GHI', 77)
INSERT INTO tblProducts VALUES (4, 'JKL', 88)
INSERT INTO tblProducts VALUES (5, 'MNO', 99)

CREATE TABLE [dbo].[tblProductDetai ls] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)

CREATE TABLE [dbo].[tblProductCompo nents] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')

CREATE TABLE [dbo].[tblProductCompo nentsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (11, 6, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (12, 7, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (13, 8, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (14, 8, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (15, 9, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (16, 9, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (17, 10, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (18, 10, 'JKLb')
CREATE TABLE [dbo].[tblProductsResu lt] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
INSERT INTO tblProductsResu lt VALUES (5, 'MNO', 99)
INSERT INTO tblProductsResu lt VALUES (6, 'GHI2', 77)
INSERT INTO tblProductsResu lt VALUES (7, 'GHI3', 77)
INSERT INTO tblProductsResu lt VALUES (8, 'JKL2', 88)
INSERT INTO tblProductsResu lt VALUES (9, 'JKL3', 88)
INSERT INTO tblProductsResu lt VALUES (10, 'JKL4', 88)
CREATE TABLE [dbo].[tblProductDetai lsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

INSERT INTO tblProductDetai lsResult VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai lsResult VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai lsResult VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai lsResult VALUES (4, 'GHI2', 6)
INSERT INTO tblProductDetai lsResult VALUES (5, 'GHI3', 7)
INSERT INTO tblProductDetai lsResult VALUES (6, 'JKL2', 8)
INSERT INTO tblProductDetai lsResult VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai lsResult VALUES (8, 'JKL3', 9)
INSERT INTO tblProductDetai lsResult VALUES (9, 'JKL4', 10)
Thanks again
You can rename your table tblproducts using sp_rename then use something
like:

INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
select (SELECT COUNT(*)
from [dbo].[oldtblProducts] Q
JOIN [dbo].tblProductDeta ils E ON E.[ProductID] = Q.[UniqueID]
WHERE D.[ProductID] > E.[ProductID]
OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS UniqueID, D.Name,P.TagNo
from [dbo].[oldtblProducts] P
JOIN [dbo].tblProductDeta ils D ON D.[ProductID] = P.[UniqueID]


Jul 23 '05 #3
You have NULL-able columns for everything, no DRI to enforce the
1-to-many relationship you say is there and no relational keys
(IDENTITY is an exposed physical locator and cannot be a key by
definition). Why do keep saying "records", when a table has rows,
which are completely different things?
The goal is to update the name of the Product by giving it the name of the first matching Name from ProductDetails <<


How do you define this matching? Since tables have no ordering what
does "first" mean? MIN()? That coudl be done with a UPDATE if you had
real keys.

Doesn't your industry have a standard part number system? Doesn't your
company use it or have one of their own? The classic schema design
for this problem usually looks more like this:

CREATE TABLE Products
(product_id INTEGER NOT NULL PRIMARY KEY, -- industry std?
product_name VARCHAR (80) NOT NULL, -- really that long?
tag_nbr INTEGER NOT NULL); -- no constraints?

CREATE TABLE ProductDetails
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
detail_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, detail_name));

CREATE TABLE ProductComponen ts
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
component_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, component_name) );

Begin by loading Products and get rid of the IDENTITY column, of
course. Then load the other tables that reference it.

Jul 23 '05 #4
I am currently working on upgrading a production database
from an older version to a new version. I inherited the processes
of how the upgrade is done currently. In this case this part a manual
"record cleanup" is the process and I need to fix the records one by
one so I wanted to find a code-based upgrade method instead.

The tables in this case are sample tables i created to try to match
the real tables I am dealing with. I can't make any changes to any of the
database structure. I only need to "clean" it up and prepare for the upgrade
scripts that are already written previously.

So the logical questions of why the tables are like this is not something
I can do to change/update or correct.

You gave me an idea that i didn't think of. Maybe I can use
UPDATE CASCADE on the tables to do some of the update work.

Thanks
"--CELKO--" <jc*******@eart hlink.net> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...
You have NULL-able columns for everything, no DRI to enforce the
1-to-many relationship you say is there and no relational keys
(IDENTITY is an exposed physical locator and cannot be a key by
definition). Why do keep saying "records", when a table has rows,
which are completely different things?
The goal is to update the name of the Product by giving it the name of
the first matching Name from ProductDetails <<
How do you define this matching? Since tables have no ordering what
does "first" mean? MIN()? That coudl be done with a UPDATE if you had
real keys.

Doesn't your industry have a standard part number system? Doesn't your
company use it or have one of their own? The classic schema design
for this problem usually looks more like this:

CREATE TABLE Products
(product_id INTEGER NOT NULL PRIMARY KEY, -- industry std?
product_name VARCHAR (80) NOT NULL, -- really that long?
tag_nbr INTEGER NOT NULL); -- no constraints?

CREATE TABLE ProductDetails
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
detail_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, detail_name));

CREATE TABLE ProductComponen ts
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
component_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, component_name) );

Begin by loading Products and get rid of the IDENTITY column, of
course. Then load the other tables that reference it.


Jul 23 '05 #5
Hi

It sounds like the easiest way to do this is to drop the FKs (ALTER TABLE
statement) or make them CASCADING. Then have a table of old keys and new
keys and issue an update statement to change the values. You can then insert
the new values that do not already exist.

John
"serge" <se****@nospam. ehmail.com> wrote in message
news:N5******** *************@w agner.videotron .net...
Hi John,

Thanks for the code.

I ran your code and even though the code looks very nice (a single Insert
statement), it doesn't work in my case. If you compare my
tblProductsResu lt
and the result generated by your code, the UniqueIDs are values that I
can't
modify for the already existing records in tblProducts because they are
referenced
in other tables. My tblProducts UniqueID 4 is "JKL", after running your
code
the
UniqueID 4 is "GHI2" which would cause problems for me.

I also completely forgot to mention a bigger problem in my original post.
I would also need to update the ProductID values in tblProductDetai ls for
all
the records that are being created in tblProducts. So everytime a new
record
is created in tblProducts I will need to get the new Identity value of
tblProducts
and update the ProductID in tblProductDetai ls.

I also just noticed the code I had originally posted doesn't have the
product
'MNO' for my sample example.

Here's the whole new code again if anyone is interested to help me out.
I thought there could be a way to do this using a few update and insert
statements.
I suspect now that maybe it is possible but much harder to write than to
write
some type of a looping through the records one by one cursor and do
UPDATE or INSERT statements one record at a time.

Here's the current code I have for the sample records:

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO

CREATE TABLE [dbo].[tblProducts] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProducts VALUES (1, 'ABC', 55)
INSERT INTO tblProducts VALUES (2, 'DEF', 66)
INSERT INTO tblProducts VALUES (3, 'GHI', 77)
INSERT INTO tblProducts VALUES (4, 'JKL', 88)
INSERT INTO tblProducts VALUES (5, 'MNO', 99)

CREATE TABLE [dbo].[tblProductDetai ls] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)

CREATE TABLE [dbo].[tblProductCompo nents] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')

CREATE TABLE [dbo].[tblProductCompo nentsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (11, 6, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (12, 7, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (13, 8, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (14, 8, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (15, 9, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (16, 9, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (17, 10, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (18, 10, 'JKLb')
CREATE TABLE [dbo].[tblProductsResu lt] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
INSERT INTO tblProductsResu lt VALUES (5, 'MNO', 99)
INSERT INTO tblProductsResu lt VALUES (6, 'GHI2', 77)
INSERT INTO tblProductsResu lt VALUES (7, 'GHI3', 77)
INSERT INTO tblProductsResu lt VALUES (8, 'JKL2', 88)
INSERT INTO tblProductsResu lt VALUES (9, 'JKL3', 88)
INSERT INTO tblProductsResu lt VALUES (10, 'JKL4', 88)
CREATE TABLE [dbo].[tblProductDetai lsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

INSERT INTO tblProductDetai lsResult VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai lsResult VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai lsResult VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai lsResult VALUES (4, 'GHI2', 6)
INSERT INTO tblProductDetai lsResult VALUES (5, 'GHI3', 7)
INSERT INTO tblProductDetai lsResult VALUES (6, 'JKL2', 8)
INSERT INTO tblProductDetai lsResult VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai lsResult VALUES (8, 'JKL3', 9)
INSERT INTO tblProductDetai lsResult VALUES (9, 'JKL4', 10)
Thanks again
You can rename your table tblproducts using sp_rename then use something
like:

INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
select (SELECT COUNT(*)
from [dbo].[oldtblProducts] Q
JOIN [dbo].tblProductDeta ils E ON E.[ProductID] = Q.[UniqueID]
WHERE D.[ProductID] > E.[ProductID]
OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS

UniqueID,
D.Name,P.TagNo
from [dbo].[oldtblProducts] P
JOIN [dbo].tblProductDeta ils D ON D.[ProductID] = P.[UniqueID]


Jul 23 '05 #6
serge (se****@nospam. ehmail.com) writes:
I ran your code and even though the code looks very nice (a single
Insert statement), it doesn't work in my case. If you compare my
tblProductsResu lt and the result generated by your code, the UniqueIDs
are values that I can't modify for the already existing records in
tblProducts because they are referenced in other tables. My tblProducts
UniqueID 4 is "JKL", after running your code the UniqueID 4 is "GHI2"
which would cause problems for me.

I also completely forgot to mention a bigger problem in my original
post. I would also need to update the ProductID values in
tblProductDetai ls for all the records that are being created in
tblProducts. So everytime a new record is created in tblProducts I will
need to get the new Identity value of tblProducts and update the
ProductID in tblProductDetai ls.

I also just noticed the code I had originally posted doesn't have the
product 'MNO' for my sample example.

Here's the whole new code again if anyone is interested to help me out.
I thought there could be a way to do this using a few update and insert
statements. I suspect now that maybe it is possible but much harder to
write than to write some type of a looping through the records one by
one cursor and do UPDATE or INSERT statements one record at a time.


Below is a script that almost produces the result you are asking for.
The difference from your reuslts table is that Product 4 now gets the
name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
each other, my guess is that it doesn't matter. If it does, it can
probably be handled, but I didn't want to spend time to find how it
it's not needed.

-- A mapping table from old product id to new product id, as well as from
-- detail id to new product id. The ident colunm is there to help us with
-- the new ids.
CREATE TABLE #newprod(ident int IDENTITY,
oldprodid int NOT NULL,
detailid int NOT NULL,
name varchar(80) NOT NULL,
newprodid int NULL)

-- Insert all details where there detail id is bigger than the min
-- detail-id for for a product.
INSERT #newprod (oldprodid, detailid, name)
SELECT a.ProductID, a.UniqueID, a.Name
FROM tblProductDetai ls a
WHERE NOT EXISTS (SELECT *
FROM (SELECT id = MIN(UniqueID)
FROM tblProductDetai ls
GROUP BY ProductID) AS b
WHERE a.UniqueID = b.id)
ORDER BY a.UniqueID

-- Set new the productid.
UPDATE #newprod
SET newprodid = n.ident + (SELECT MAX(UniqueID) FROM tblProducts)
FROM #newprod n

-- Create the clones. (If the real-world table has IDENTITY, you need
-- SET IDENTITY_INSERT ON here.
INSERT tblProducts (UniqueID, Name, TagNo)
SELECT n.newprodid, p.Name, p.TagNo
FROM #newprod n
JOIN tblProducts p ON n.oldprodid = p.UniqueID

-- Update details table with the new Product ids.
UPDATE tblProductDetai ls
SET ProductID = n.newprodid
FROM tblProductDetai ls d
JOIN #newprod n ON d.UniqueID = n.detailid

-- Set product name to match details.
UPDATE tblProducts
SET Name = d.Name
FROM tblProducts p
JOIN tblProductDetai ls d ON p.UniqueID = d.ProductID

-- And go on to clone components. Again there is an intermediate table
-- to get the new identity values. Had the Component tables had an
-- IDENTITY column, we would not need this table.
CREATE TABLE #newcomp(ident int IDENTITY,
newprodid int NOT NULL,
component varchar(80) NOT NULL)

INSERT #newcomp(newpro did, component)
SELECT n.newprodid, c.Component
FROM tblProductCompo nents c
JOIN #newprod n ON c.ProductID = n.oldprodid
ORDER BY n.newprodid, c.Component

INSERT tblProductCompo nents (UniqueID, ProductID, Component)
SELECT n.ident + m.maxid, n.newprodid, n.component
FROM #newcomp n
CROSS JOIN (SELECT maxid = MAX(UniqueID)
FROM tblProductCompo nents) AS m

-- Look at the result
SELECT * FROM tblProducts ORDER BY UniqueID
SELECT * FROM tblProductDetai ls ORDER BY UniqueID
SELECT * FROM tblProductCompo nents ORDER BY UniqueID


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
> The difference from your reuslts table is that Product 4 now gets the
name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
each other, my guess is that it doesn't matter.


I am 99% sure it does not matter. I'll find out soon if it matters but I
doubt it.

I tried your code and it works great. I analyzed it for the last hour to
understand what you were doing. Now I understand and I didn't consider
before using a temporary table like you are using. I was planning on
using a cursor and loop through the records one by one which would have
been much slower than your approach. Not to mention it would have been
complicated also.

Next time I'll think about using temporary tables as I realize now how
much flexibility it gave you to do the gradual data manipulations.
Thanks a lot Erland, I appreciate your help.


Jul 23 '05 #8
serge (se****@nospam. ehmail.com) writes:
I tried your code and it works great. I analyzed it for the last hour to
understand what you were doing. Now I understand and I didn't consider
before using a temporary table like you are using. I was planning on
using a cursor and loop through the records one by one which would have
been much slower than your approach. Not to mention it would have been
complicated also.

Next time I'll think about using temporary tables as I realize now how
much flexibility it gave you to do the gradual data manipulations.


The true die-hards does of course try to all in one query, and stay
away from temp tables. I am more pragmatic, and interested in getting
the job done as effectively as possible.

The problem could probably be solved with out temp tables, if you don't
care about the new ids to be consecutive. The new product ids could be
formed by adding MAX(ProductID) to the detail id. But since you real
tables have IDENTITY, I figured that you could get into some problems
with that.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
> Below is a script that almost produces the result you are asking for.
The difference from your reuslts table is that Product 4 now gets the
name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
each other, my guess is that it doesn't matter. If it does, it can
probably be handled, but I didn't want to spend time to find how it
it's not needed.
Actually it seems that the name does matter. There is a unique index on
the NAME column on tblProducts. You make your code look easy and
simple to write. I tried to modify this part of the code below to exclude
matching names from the selection list by adding a JOIN to tblProducts.
I was unsuccessful after 3 hours of trying. I have people pressing me to
give up writing code and upgrade the data manually one record at a time.
What a nightmare!

I then thought about deleting temporarily the Unique Index and running the
code which will create duplicate records without any error. Then the UPDATE
Names code I believe is fixing/patching things up. However i am at this
point
unsure if i will have to face a new problem when it comes to the other CHILD
table references that I have to create clone records.
SELECT a.ProductID, a.UniqueID, a.Name
FROM tblProductDetai ls a
WHERE NOT EXISTS (SELECT *
FROM (SELECT id = MIN(UniqueID)
FROM tblProductDetai ls
GROUP BY ProductID) AS b
WHERE a.UniqueID = b.id)
ORDER BY a.UniqueID


If you believe you can update the code very easily to handle the Name
matter,
would it be possible to do it please?

Thank you


Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1778
by: nss | last post by:
hi, I created web page ( .aspx) then i imported datagrid on that page with dataset. Using OleDbDataAdapter i made select qurey when page is launch it shows row. But i wanna update and insert new record in the database via datagrid how it done regards, Sam
2
21373
by: Tinius | last post by:
I am trying to update a field of type IMAGE. I am able to do this by deleting the record and then INSERTing a new record as shown below in the commented section. But I really want to do this with an UPDATE command as shown below in the un-commented code. I assume this is possible. The image is passed as a byte array called 'content'. I then want to update the column STREAM with the new 'CONTENT'.
0
1103
by: mattie | last post by:
hey all, when you do a SqlDataAdapter.Update(DataTable) command and if the record is an insert how do you obtain the new row that gets selected immediately after the insert command? thanks, mj
4
1509
by: Diana Estrada | last post by:
Hi, I have a store procedure to insert , and I use tableadapter to execute this store procedure, when I run the application and execute the sp , works ok and after I see the datagridview and the new row is there, but the problem ir that when I stop the application and look my database in SQL Server 2005 the table don´t have the new row. Somebody knows why? or Any idea to execute the store procedure to insert or delete or update rows in...
4
1502
by: Treefrog | last post by:
Hi all, I came accross a little piece of code today that I thought was quite cute, but I'm not sure if it's any better than my usual method. The code ultimately needs to see if a unique identifier exists in a database, then either insert or update accordingly. The way I've always done it is: SELECT * FROM blah where.....
0
1245
by: Bert | last post by:
Hi I am looking for a sample code for a gridview/detailsview in which I can insert, update and delete rows through stored procedures? thanks B
0
1240
by: debnath1981 | last post by:
Insert, Update, Delete through DataGrid
5
1382
by: qatarya3sal | last post by:
Imports System.XML Public Class Form1 Inherits System.Windows.Forms.Form Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load transactions = New Transaction() {} GCC.Open() End Sub
6
3680
by: sgulciny | last post by:
hi friends; I have problem about sql server insert and update in client side. I am coding windows application with c#.When I run my code in database server computer all is fine.I can see data, insert and update.But when I run same code in client side I can see data but not insert not update what can I do about this problem .I use sql server 2000 database.Client side connections ok. :( thanks for all
4
2395
by: Barno77 | last post by:
Everytime I edit a record, and hit update I get duplicating lines in my Details View. Here's my If statement: If (Len(e.OldValues("AutoNumber")) > 0) Then sql = "UPDATE SHIP_LOG SET FileNumber='" & e.NewValues("FileNumber") & "',BOL='" & e.NewValues("BOL") & "',Container='" & e.NewValues("Container") & "',DESTN='" & e.NewValues("DESTN") & "', Comments='" & e.NewValues("Comments") & "' WHERE POD_COMINV='" & e.Keys("POD_COMINV") &...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9054
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8897
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7785
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6549
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4390
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3071
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2362
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2015
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.