• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1347
  • Last Modified:

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..
0
VilkoP
Asked:
VilkoP
1 Solution
 
JimBrandleyCommented:
You could drop the foreign key constraints on the other tables, then rebuild them with ON UPDATE CASCADE.
0
 
LowfatspreadCommented:
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
0
 
VilkoPAuthor Commented:
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..
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
bamboo7431Commented:
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.
0
 
dbbishopCommented:
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,
0
 
VilkoPAuthor Commented:
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..
0
 
dbbishopCommented:
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;

Lets pick one. In this example you will be resizing exising fields to hold the new data, but you can just as easily just add a [BARCode] column to the revelant tables. The coihce is yours.

Alter the table to change the length of [STOCKCODE] to 30.

Then run this query:
UPDATE  TABLE [dbo].[BATCH_QUANTITIES]
SET         STOCKCODE = [dbo].[STOCK_ITEMS].[BARCODE1]
FROM      [dbo].[STOCK_ITEMS]
WHERE   [dbo].[BATCH_QUANTITIES].STOCKCODE =  [dbo].[STOCK_ITEMS].[STOCKCODE]

Now, the FK candidate lies between  [dbo].[STOCK_ITEMS].BARCODE1] and [dbo].[BATCH_QUANTITIES]
[STOCKCODE] even though the column names do not represent the relationship. You can use ALTER TABLE again to change the column name for [dbo].[BATCH_QUANTITIES].[STOCKCODE]  to [BacthCode] to make it intuititve.

I mentioned adding a [BARCODE] column to the tables that don't have one. The might be easier than changing the format of the [STOCKCODE] column and keeps all data intact unil you are certain everything worked properly. You can even define multiple FK relationships and verify you are still getting all of your data properly. Naturally, there is more than one way to properly do this. Sometimes you can narrow down the best way by knowing the data (.i.e. if you have 5-million rows of data and are going to be updating clustered PK, there is a very good chance you will want to drop the indexes first, make the data changes, then rebuild the indexes. If it is 3,500 rows, leave the structure in tact.

Whatever you do, make sure data types and lengths match up. SQL is pretty good about advising you if you will lose data, but moving a 30-charactrer key field int a 27-character key field is disaster waiting to happen.
0
 
VilkoPAuthor Commented:
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now