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
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
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
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
ASKER
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'.
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'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
please watch for my second post, it is almost the same thing, but the script is for a delete record.
Thanks
CREATE TABLE zzTableNames (i int identity, TableName nvarchar(100), columnName nvarchar(100) )
INSERT INTO zzTableNames
SELECT TABLE_NAME, column_name FROM INFORMATION_SCHEMA.COLUMNS
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