query query (if u know what i mean!)
-
Hi,
I have 2 x tables: PRODUCT & ACCOUNT_PRODUCT
Unfortunatley, due to an old problem, there are many products that have been duplicated in the main PRODUCT table eg:
PRODUCTID CODE
A1 BOX-BIG
A2 BOX-BIG
A3 BOX-BIG
.... ETC
I would like some script that DELETES from PRODUCT where the PRODUCTID is NOT being used in the ACCOUNT_PRODUCT table, but i need to keep 1 x instance of the product code in the PRODUCT table.
Further to this, the product code may, or may not, have an entry in the ACCOUNT_PRODUCT table but I still need to clear down the duplicates from the PRODUCT table and keep 1 x instance of the product in the main PRODUCT table
I hope i am making sense with this - any ideas ?
Mark
-
First is ACCOUNT_PRODUCT the only table that will have a reference to PRODUCT ?
If so I suggest something like this.
CREATE TABLE dbo.Temp_Products (
PRODUCTID char(2) NOT NULL,
CODE VARCHAR(30) NOT NULL PRIMARY KEY
)
GO
INSERT dbo.Temp_Products (PRODUCTID, CODE) SELECT Min(PRODUCTID), CODE FROM dbo.PRODUCT GROUP BY CODE
GO
UPDATE
A
SET
PRODUCTID = C.PRODUCTID
FROM
dbo.ACCOUNT_PRODUCT A
INNER JOIN
dbo.PRODUCT B
INNER JOIN
dbo.Temp_Products C
ON
B.CODE = C.CODE
ON
A.PRODUCTID = B.PRODUCTID
GO
DELETE dbo.PRODUCT WHERE PRODUCTID NOT IN (SELECT X.PRODUCTID FROM dbo.Temp_Products) -- Note various ways to write this may not be optimal, need to know the number of records.
GO
DROP TABLE dbo.Temp_Products
GO
/* Used EM to product this */
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.PRODUCT ADD CONSTRAINT
IX_PRODUCT_UNIQUE_CODE UNIQUE NONCLUSTERED
(
CODE
 
ON [PRIMARY]GO
COMMIT
What this should do is generate the table like you should have had to begin with and narrows to 1 option, then replaces the link in ACCOUNT_PRODUCT to the one we want to retain. Then removes all the duplicates left over. Then lastly put code in place to prevent data from being duplicated so easily again.
-
Also, before I forget. Make sure you test on a none production machine, backup the database before actually running, and use a BEGIN TRAN before you run this part
CREATE TABLE dbo.Temp_Products (
PRODUCTID char(2) NOT NULL,
CODE VARCHAR(30) NOT NULL PRIMARY KEY
)
GO
INSERT dbo.Temp_Products (PRODUCTID, CODE) SELECT Min(PRODUCTID), CODE FROM dbo.PRODUCT GROUP BY CODE
GO
UPDATE
A
SET
PRODUCTID = C.PRODUCTID
FROM
dbo.ACCOUNT_PRODUCT A
INNER JOIN
dbo.PRODUCT B
INNER JOIN
dbo.Temp_Products C
ON
B.CODE = C.CODE
ON
A.PRODUCTID = B.PRODUCTID
GO
DELETE dbo.PRODUCT WHERE PRODUCTID NOT IN (SELECT X.PRODUCTID FROM dbo.Temp_Products) -- Note various ways to write this may not be optimal, need to know the number of records.
GO
DROP TABLE dbo.Temp_Products
GO
Do a few selects to verify the results came thru (have to use the open QA window where you ran the transaction and then use COMMIT TRAN by itself. Do not close the QA child window until you do the steps.
-
Excuse the expletive but, bloody hell !!!
That's what i call a reply !!!
Thankyou very much for this, it's exactly what i was looking for
Best regards,
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply