Hi,
We have a management system that tracks information about different companies for various products. A specific account might consist of a number of different companies. The detail information about each company can be retrieved from the CompanyProductDetails table. If you load the below data, you will see that account '1' is associated with product 'A'. For product 'A', company '1' and '2' have various detail records.
This is the problem. For an account, the product might change. If this occurs, I need to do perform two steps. first delete all the details records from AccountDetails for the old product that doesn't also exist for the new product. Second, after deleteing the old records, I need to insert into AccountDetails all the new details that don't currently exist. The main thing is the items that are incommon between products A and B must remain in the account details table. I just can't delete all the records and insert new ones (in reality, there are child tables associated with the detail so I can't loose the data).
In the attached example, if switching from product A to B:
Step 1
for company1 delete detail 2 and 3 from AccountDetails
for company2 delete detail 2 and 3 from AccountDetails
Step 2
for company1 insert detail 4 and 5 into AccountDetails
for company2 insert detail 5 into AccountDetails
To make a long question short, detail 1 is shared between productA and B so must be left alone .
I am stumped on how the sql queries necessary do this. any help would be appreciated.
CREATE TABLE CompanyProductDetails (
[CompanyID] [varchar] (10),
[Product] [varchar] (10),
[DetailItem] [varchar] (10)
)
GO
CREATE TABLE AccountProducts (
[AccountID] [varchar] (10),
[Product] [varchar] (10)
)
GO
CREATE TABLE AccountDetails (
[AccountID] [varchar] (10),
[CompanyID] [varchar] (10),
[DetailItem] [varchar] (10)
)
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodA','detail1')
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodA','detail2')
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodA','detail3')
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodA','detail1')
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodA','detail2')
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodA','detail3')
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodB','detail1')
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodB','detail4')
GO
INSERT INTO CompanyProductDetails VALUES ('comp1','prodB','detail5')
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodB','detail1')
GO
INSERT INTO CompanyProductDetails VALUES ('comp2','prodB','detail5')
GO
INSERT INTO AccountProducts VALUES('acc1','prodA')
GO
INSERT INTO AccountDetails VALUES ('acc1','comp1','detail1')
GO
INSERT INTO AccountDetails VALUES ('acc1','comp1','detail2')
GO
INSERT INTO AccountDetails VALUES ('acc1','comp1','detail3')
GO
INSERT INTO AccountDetails VALUES ('acc1','comp2','detail1')
GO
INSERT INTO AccountDetails VALUES ('acc1','comp2','detail2')
GO
INSERT INTO AccountDetails VALUES ('acc1','comp2','detail3')
GO
Nic
add and remove items without affecting other items
Posted - 04/16/2004 : 11:00:07
