Solved

Modify SQL Script for modifying record

Posted on 2013-01-30
4
362 Views
Last Modified: 2013-01-30
Hello Experts,

I currently have this SQL script that goes through all my tables in the database and modifies the item I specify from one item to another. It is working well for me so far.
It searches for the field name "ITEMNMBR". But I just found out that the item I need modified is also located as a different field name "ITEMNO".
I would like to modify the script to be able to look at both fields for the item I want to modify.

Thanks
*****************************************************************

USE BTF
CREATE TABLE zzTableNames (TableName nvarchar(100))
INSERT INTO zzTableNames
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'ITEMNMBR'
DECLARE @TableName nvarchar(100),@Query nvarchar(100)
SET @TableName = ''
WHILE @TableName IS NOT NULL
BEGIN
SELECT @TableName = (SELECT MIN(TableName) FROM zzTableNames WHERE TableName > @TableName)
IF @TableName IS NOT NULL
BEGIN
SET @Query = 'UPDATE ' + @TableName + ' SET ITEMNMBR = ''01AAABBBCCC-XX'' WHERE ITEMNMBR = ''02DDDEEEFF-YY'''
EXEC(@Query)  
END
END

DROP TABLE zzTableNames
0
Comment
Question by:jann3891
  • 2
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
DECLARE @TableName nvarchar(100),@Query nvarchar(100),@ColumnName nvarchar(100),@i int
CREATE TABLE zzTableNames (i int identity, TableName nvarchar(100), columnName nvarchar(100) )
INSERT INTO zzTableNames
SELECT TABLE_NAME, column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ( 'ITEMNMBR', 'ITEMNO')
SELECT @i =@@ROWCOUNT
SET @TableName = ''
WHILE @i > 0 L
BEGIN
SELECT @TableName = TableName , @ColumnName = columnName
FROM zzTableNames
WHERE i = @i
SET @i =@i-1
IF @TableName IS NOT NULL
BEGIN
SET @Query = 'UPDATE ' + @TableName + ' SET ITEMNMBR = ''01AAABBBCCC-XX'' WHERE ' +@ColumnName+' = ''02DDDEEEFF-YY'''
EXEC(@Query)  
END
END

DROP TABLE zzTableNames
0
 

Author Comment

by:jann3891
Comment Utility
Thank you for the quick response.
i have a couple of questions.

WHILE @i > 0 L
is that "L" suppose to be there?  I am getting an error.
  Server: Msg 170, Level 15, State 1, Line 7
  Line 7: Incorrect syntax near 'L'.

so, I removed the L, query batch completed with errors.
  Server: Msg 207, Level 16, State 1, Line 1
  Invalid column name 'ITEMNMBR'.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
DECLARE @TableName nvarchar(100),@Query nvarchar(100),@ColumnName nvarchar(100),@i int
CREATE TABLE zzTableNames (i int identity, TableName nvarchar(100), columnName nvarchar(100) )
INSERT INTO zzTableNames
SELECT TABLE_NAME, column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ( 'ITEMNMBR', 'ITEMNO')
SELECT @i =@@ROWCOUNT
SET @TableName = ''
WHILE @i > 0
BEGIN
SELECT @TableName = TableName , @ColumnName = columnName
FROM zzTableNames
WHERE i = @i
SET @i =@i-1
IF @TableName IS NOT NULL
BEGIN
SET @Query = 'UPDATE ' + @TableName + ' SET '+@ColumnName+' = ''01AAABBBCCC-XX'' WHERE ' +@ColumnName+' = ''02DDDEEEFF-YY'''
EXEC(@Query)  
END
END

DROP TABLE zzTableNames
0
 

Author Closing Comment

by:jann3891
Comment Utility
works great. Thank you.
please watch for my second post, it is almost the same thing, but the script is for a delete record.

Thanks
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now