Modify SQL Script for modifying record

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
jann3891Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
jann3891Author Commented:
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
 
jann3891Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.