asked on

SQL Script - Multiple table update (including PK and Composite PK) SQL Server 2005

I have a sql server 2005 database that has several hundred tables. One table has a field called stockcode which is the primary key in Stock_Item Table. We need to replace that value with a new value which will reflect the change that we want to implement of being able to have our own barcode id (which is stored in the stockitem table). At the moment another field in the same table called barcode_1 has that new info which we generated. We ned to swap two fields arround, which is not a big deal, but (and there is always a BUT...) the field stockcode appears in another about 20 tables. Stockcode is Composite PK in 6 other tbales, and is found in another 13 tables. Same field exists in another 23 tables but they do not have any data in it (so we would not need to worry about them).
How do i go about this, what do you need from mein order to work out the solution..

Original table
CREATE TABLE [dbo].[STOCK_ITEMS](
      [STOCKCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [DESCRIPTION] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STOCKGROUP] [int] NULL CONSTRAINT [DF__STOCK_ITE__STOCK__11F49EE0] DEFAULT ((0)),
      [STATUS] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__STOCK_ITE__STATU__12E8C319] DEFAULT ('L'),
      [SELLPRICE1] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__13DCE752] DEFAULT ((0)),
      [SELLPRICE2] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__14D10B8B] DEFAULT ((0)),
      [SELLPRICE3] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__15C52FC4] DEFAULT ((0)),
      [SELLPRICE4] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__16B953FD] DEFAULT ((0)),
      [SELLPRICE5] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__39987BE6] DEFAULT ((0)),
      [SELLPRICE6] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__3A8CA01F] DEFAULT ((0)),
      [SELLPRICE7] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__3B80C458] DEFAULT ((0)),
      [SELLPRICE8] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__3C74E891] DEFAULT ((0)),
      [SELLPRICE9] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__3D690CCA] DEFAULT ((0)),
      [SELLPRICE10] [float] NULL CONSTRAINT [DF__STOCK_ITE__SELLP__3E5D3103] DEFAULT ((0)),
      [LATESTCOST] [float] NULL CONSTRAINT [DF__STOCK_ITE__LATES__17AD7836] DEFAULT ((0)),
      [AVECOST] [float] NULL CONSTRAINT [DF__STOCK_ITE__AVECO__18A19C6F] DEFAULT ((0)),
      [MINSTOCK] [float] NULL CONSTRAINT [DF__STOCK_ITE__MINST__24134F1B] DEFAULT ((0)),
      [MAXSTOCK] [float] NULL CONSTRAINT [DF__STOCK_ITE__MAXST__25077354] DEFAULT ((0)),
      [SUPPLIERNO] [int] NULL CONSTRAINT [DF__STOCK_ITE__SUPPL__25FB978D] DEFAULT ((0)),
      [MONTHUNITS] [float] NULL CONSTRAINT [DF__STOCK_ITE__MONTH__26EFBBC6] DEFAULT ((0)),
      [YEARUNITS] [float] NULL CONSTRAINT [DF__STOCK_ITE__YEARU__27E3DFFF] DEFAULT ((0)),
      [LASTYEARUNITS] [float] NULL CONSTRAINT [DF__STOCK_ITE__LASTY__28D80438] DEFAULT ((0)),
      [MONTHVALUE] [float] NULL CONSTRAINT [DF__STOCK_ITE__MONTH__29CC2871] DEFAULT ((0)),
      [YEARVALUE] [float] NULL CONSTRAINT [DF__STOCK_ITE__YEARV__2AC04CAA] DEFAULT ((0)),
      [LASTYEARVALUE] [float] NULL CONSTRAINT [DF__STOCK_ITE__LASTY__2BB470E3] DEFAULT ((0)),
      [BINCODE] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [DISCOUNTLEVEL] [int] NULL CONSTRAINT [DF__STOCK_ITE__DISCO__2CA8951C] DEFAULT ((0)),
      [DEFDAYS] [int] NULL CONSTRAINT [DF__STOCK_ITE__DEFDA__2D9CB955] DEFAULT ((0)),
      [BARCODE1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BARCODE2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [BARCODE3] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LASTMONTHVALUE] [float] NULL CONSTRAINT [DF__STOCK_ITE__LASTM__2E90DD8E] DEFAULT ((0)),
      [LASTMONTHUNITS] [float] NULL CONSTRAINT [DF__STOCK_ITE__LASTM__2F8501C7] DEFAULT ((0)),
      [SALES_GL_CODE] [int] NULL CONSTRAINT [DF__STOCK_ITE__SALES__46335CF5] DEFAULT ((0)),
      [PURCH_GL_CODE] [int] NULL CONSTRAINT [DF__STOCK_ITE__PURCH__4727812E] DEFAULT ((0)),
      [WEB_SHOW] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__STOCK_ITE__WEB_S__44160A59] DEFAULT ('N'),
      [ISACTIVE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__STOCK_ITE__ISACT__74B941B4] DEFAULT ('Y'),
      [WEIGHT] [float] NULL CONSTRAINT [DF__STOCK_ITE__WEIGH__44D52468] DEFAULT ((0)),
      [CUBIC] [float] NULL CONSTRAINT [DF__STOCK_ITE__CUBIC__45C948A1] DEFAULT ((0)),
      [ALERT] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [NOTES] [varchar](4096) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PQTY] [float] NULL CONSTRAINT [DF__STOCK_ITEM__PQTY__7C255952] DEFAULT ((1)),
      [PACK] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [HAS_SN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__STOCK_ITE__HAS_S__7FF5EA36] DEFAULT ('N'),
      [STDCOST] [float] NULL CONSTRAINT [DF__STOCK_ITE__STDCO__392E6792] DEFAULT ((0)),
      [SUPPLIERNO2] [int] NULL,
      [SUPPLIERNO3] [int] NULL,
      [SALES_GLSUBCODE] [int] NULL CONSTRAINT [DF__STOCK_ITE__SALES__442B18F2] DEFAULT ((0)),
      [PURCH_GLSUBCODE] [int] NULL CONSTRAINT [DF__STOCK_ITE__PURCH__451F3D2B] DEFAULT ((0)),
      [BRANCHNO] [int] NULL CONSTRAINT [DF__STOCK_ITE__BRANC__5FD33367] DEFAULT ((0)),
      [SALESTAXRATE] [int] NULL CONSTRAINT [DF__STOCK_ITE__SALES__6774552F] DEFAULT ((0)),
      [PURCHTAXRATE] [int] NULL CONSTRAINT [DF__STOCK_ITE__PURCH__68687968] DEFAULT ((0)),
      [LAST_UPDATED] [datetime] NULL,
      [UPDATEITEM_CODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [UPDATEITEM_QTY] [float] NULL CONSTRAINT [DF__STOCK_ITE__UPDAT__257187A8] DEFAULT ((0)),
      [COS_GL_CODE] [int] NULL CONSTRAINT [DF__STOCK_ITE__COS_G__4D7F7902] DEFAULT ((0)),
      [COS_GLSUBCODE] [int] NULL CONSTRAINT [DF__STOCK_ITE__COS_G__4E739D3B] DEFAULT ((0)),
      [STOCKPRICEGROUP] [int] NULL CONSTRAINT [DF__STOCK_ITE__STOCK__69279377] DEFAULT ((-1)),
      [SUPPLIERCOST] [float] NOT NULL CONSTRAINT [DF__STOCK_ITE__SUPPL__16B953FD] DEFAULT ((0)),
      [ECONORDERQTY] [float] NULL,
      [LINKED_BILLCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [STOCK_CLASSIFICATION] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__STOCK__6442E2C9] DEFAULT ((0)),
      [STOCKGROUP2] [int] NULL CONSTRAINT [DF__STOCK_ITE__STOCK__4F87BD05] DEFAULT ('0'),
      [TOTALSTOCK] [float] NOT NULL CONSTRAINT [DF__STOCK_ITE__TOTAL__51700577] DEFAULT ((0)),
      [HAS_BN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__STOCK_ITE__HAS_B__526429B0] DEFAULT ('N'),
      [HAS_EXPIRY] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__STOCK_ITE__HAS_E__6576FE24] DEFAULT ('N'),
      [EXPIRY_DAYS] [int] NULL CONSTRAINT [DF__STOCK_ITE__EXPIR__666B225D] DEFAULT ((1)),
      [DUTY] [float] NOT NULL CONSTRAINT [DF__STOCK_ITEM__DUTY__675F4696] DEFAULT ((0)),
      [SERIALNO_TYPE] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__SERIA__24DD5622] DEFAULT ((0)),
      [LABEL_QTY] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__LABEL__2DB29345] DEFAULT ((1)),
      [IS_DISCOUNTABLE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__STOCK_ITE__IS_DI__2C8964E2] DEFAULT ('Y'),
      [RESTRICTED_ITEM] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__STOCK_ITE__RESTR__689E4EE9] DEFAULT ('N'),
      [NUMDECIMALS] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__NUMDE__78D4B6B2] DEFAULT ('-1'),
      [COGSMETHOD] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__COGSM__7BB1235D] DEFAULT ((0)),
      [COSTTYPE] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__COSTT__16300F6F] DEFAULT ((0)),
      [COSTGROUP] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__COSTG__172433A8] DEFAULT ((0)),
      [DEFAULTWARRANTYNO] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__DEFAU__1685152A] DEFAULT ('-1'),
      [DIMENSIONS] [int] NOT NULL CONSTRAINT [DF__STOCK_ITE__DIMEN__32EC49AE] DEFAULT ((0)),
      [TEMP_STOCKGROUP] [int] NULL,
      [X_IN_OUT] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [X_WEIGHTAGE] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SELLPRICE11] [money] NULL DEFAULT ((0)),
      [X_POWERPOINTS] [int] NULL DEFAULT ((0)),
      [X_ALT_STOCKCODE] AS ((0)+(1)),
CONSTRAINT [PK__STOCK_ITEMS__11007AA7] PRIMARY KEY CLUSTERED
(
      [STOCKCODE] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

table with it being PK
CREATE TABLE [dbo].[STOCK_WEB](
      [STOCKCODE] [char](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [SALES_HTML] [varchar](4096) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [PICTURE_URL] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
      [STOCKCODE] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

table with Comp PK..
CREATE TABLE [dbo].[SUPPLIER_STOCK_ITEMS](
      [STOCKCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [SUPPLIERCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [DESCRIPTION] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LATESTCOST] [float] NULL CONSTRAINT [DF__SUPPLIER___LATES__1FB8AE52] DEFAULT (0),
      [ACCNO] [int] NOT NULL,
      [ECONORDERQTY] [float] NULL,
      [PURCHPACKQUANT] [float] NOT NULL CONSTRAINT [DF__SUPPLIER___PURCH__68536ACF] DEFAULT (1),
      [PURCHPACKPRICE] [float] NOT NULL CONSTRAINT [DF__SUPPLIER___PURCH__69478F08] DEFAULT (0),
      [PACKREFERENCE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LAST_UPDATE] [datetime] NULL,
      [DISCOUNT] [float] NULL DEFAULT (0),
      [IS_DEFAULT] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT ('N'),
CONSTRAINT [PK__SUPPLIER_STOCK_I__1EC48A19] PRIMARY KEY CLUSTERED
(
      [STOCKCODE] ASC,
      [SUPPLIERCODE] ASC,
      [ACCNO] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Table with just Field in it :
CREATE TABLE [dbo].[BATCH_QUANTITIES](
      [SEQNO] [int] IDENTITY(1,1) NOT NULL,
      [STOCKCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LOCATION] [int] NULL,
      [BATCHCODE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [QUANTITY] [float] NULL,
      [EXPIRY_DATE] [datetime] NULL,
      [REFERENCE] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK__BATCH_QUA__SEQNO__4F87BD05] PRIMARY KEY CLUSTERED
(
      [SEQNO] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Any help or suggestions will be appreciated..

JimBrandley

You could drop the foreign key constraints on the other tables, then rebuild them with ON UPDATE CASCADE.

Lowfatspread

Hello VilkoP,

your tables don't look very normalised... e.g. the multiple barcodes, supplier ids,...

in the dependant tables you don't appear to alos have the barcode column...

please explain in more detail the background to the proposed change...

if barcode1 is unique then it can become the primary key of the first table...
assuming stockcode is still unique then it can be an alternate key on the table and remain in all the other relationships...

puzzled

Regards,

Lowfatspread

VilkoP

ASKER

hi Lowfatspread,

The tables are not normalised at all it is the way the third party software developer developed, and it is a major software company. What i am trying is to corect the data entry error that started from the first entry. It should have been done initially, but the idea about own barcodes came after the system has been in use for a while. No the barcode column only appears in the main table.
I need to swap the barcode record with the stockcode but i have to swap all the other stockcodes that appear in the oter tables too at the same time.. so if i have stock code 987654321 in the main table and i want to swap it to 10001 i need to swap it in every table where 987654321 appears and put the 10001. The complexity is that some of those tables use stock code as a PK and few have it as a composite PK. Barcode1 is a unique key. I agree with your suggestion except there is an application sitting on top of the db so i can not be sure wher enad when it may look at the db for what ever reason, so the safest is to completely replace the keys everywhere..
I hope this explains it a bit more..

bamboo7431

the laziest and the easiest decision will be to make the foreign keys ON UPDATE CASCADE

the old-school 'proper' decision would be to write a stored proc that will receive the old and new value and update it in one transaction in all tables - starting from the child ones and up to the master one.

D B

Probably best (and fastest way) would be to drop all PK and FK relationships--pretty much drop all indexes that will be candidates for changes (depends primarily on how much data this represents). Then createan update statement for each table that has STOCKCODE as a FK;

Ex:
Part of primary table:
[dbo].[STOCK_ITEMS]
(
[STOCKCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BARCODE1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Table with just Field in it :
CREATE TABLE [dbo].[BATCH_QUANTITIES](
[SEQNO] [int] IDENTITY(1,1) NOT NULL,
[STOCKCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCATION] [int] NULL,
[BATCHCODE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QUANTITY] [float] NULL,
[EXPIRY_DATE] [datetime] NULL,
[REFERENCE] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

UPDATE [dbo].[BATCH_QUANTITIES] AS Q
SET Q.StockCode = P.BARCODE1
FROM [dbo].[BATCH_QUANTITIES] AS Qyt
(INNER JOIN Qty.STOCKCODE]
WHERE
[SEQNO] [int] IDENTITY(1,1) NOT NULL,
[STOCKCODE] [varchar](23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCATION] [int] NULL,
[BATCHCODE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QUANTITY] [float] NULL,
[EXPIRY_DATE] [datetime] NULL,
[REFERENCE] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

VilkoP

ASKER

ok thanks for very constructive responce,
So let me get this right..
- i script all the Pk and FK and indexes..
- I drop them from the relevant tables
- i create a script that wil update all the fileds with the new code
- I put all PK, Fk and indexes back

i am a bit puzled byt he update statement..
shoulf it be like this:

UPDATE [dbo].[BATCH_QUANTITIES] AS Q
SET Q.StockCode = P.BARCODE1
FROM [dbo].[BATCH_QUANTITIES] AS Qty
(INNER JOIN Qty.STOCKCODE])
WHERE Q.StockCode = P.StockCode

and i can folow this for al the tables that need it updated..

ASKER CERTIFIED SOLUTION

D B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VilkoP

ASKER

hi dbbishop,

Thanks for the explanation. The amount of data is nto that great i think the biggest one is about 100,000 rows, but the stockcode has about 2,000 rows. I would not like to change the data structure as it is a third party application that sits on top of the database so we may stuff something up by changing the structure, on the other hand if we manipulate the data that should be easier,and the application would not care. I think the longest stockcode is about 10-12 characters, so the fact that the two columns are different size should not matter at all in our case.